format cell based on contents using VB

15 pts.
Conditional statements
Excel VBA
i need to change the color of a cell in a specified column based on the value e,g if A1 value is between 1 and 10 then the colo will be red, if the value is between 11-20 then the color will be green etc. i used conditional formats but they only allow for 3 variables i need a script or other that will be this for more than 3 variables. i use excel 2003

Answer Wiki

Thanks. We'll let you know when a new response is added.

Right click on the tab you wish to create the color coding on. Paste the following:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim test As Integer, results As Integer
test = Cells(ActiveCell.Row, 1).Value ‘Finds the row number of the cell you edited
Select Case test ‘changes the color of column B, of the row you are editing.
Case Is <= 0
Cells(ActiveCell.Row, 2).Interior.ColorIndex = 3 ‘Red
Case 1 To 10
Cells(ActiveCell.Row, 2).Interior.ColorIndex = 6 ‘Yellow
Case Is > 10
Cells(ActiveCell.Row, 2).Interior.ColorIndex = 43 ‘Green
End Select
End Sub

You’ll need to modify that to fit your spreadsheet some, but that is the basics. You need to hit the arrow key instead of the carriage return for this to change the correct cell color.

Discuss This Question: 3  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Voicetec
    hi thanks for this but it isn't what i was looking for..this code seems to automatically colour the cells without any input? that how it should work? What i want is for when i insert a code into each cell in a specified col the cell colur changes depending on the input. e.g say all my codes will be in col b now in row 1 col b i insert =2 (the cell color changes to red) in row 2 col b i insert =12 (the cell color changes to blue) etc the color is dependent on the range i.e 1 to 10 = red, 11 to 20 = blue etc is this making sense?
    15 pointsBadges:
  • Usmcjcr
    I'm using Excel 2003. I've tried your advice - no luck. My situation: When I place an "x" into an empty cell I want that cell to turn Yellow. Following your suggestion here is how I wrote the formula: =Cells(ActiveCell.Row, x).Interior.ColorIndex = 6 'Yellow Jim
    15 pointsBadges:
  • carlosdl
    Hi Usmcjcr. You could use this code (this is not a formula, it is VBA code):
    Private Sub Worksheet_Change(ByVal Target As Range)
        If StrConv(Target.Value, vbUpperCase) = "X" Then
            Target.Interior.ColorIndex = 6
        End If
    End Sub
    85,885 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: