format cell based on contents using VB

Voicetec

15 pts.

Tags:

Conditional statements

Excel VBA

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

Asked: July 14, 2009 10:45 AM Last updated: March 29, 2010 2:25 PM

Answer Wiki

Last updated: July 14, 2009 5:10 PM GMT

Jade1977440 pts.
History
Contributors
- Ordered by most recent
- Jade1977440 pts.

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:

<pre>
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
End
Case 1 To 10
Cells(ActiveCell.Row, 2).Interior.ColorIndex = 6 ‘Yellow
End
Case Is > 10
Cells(ActiveCell.Row, 2).Interior.ColorIndex = 43 ‘Green
End
End Select
End Sub
</pre>

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

Voicetec Jul 15, 2009 9:57 AM GMT

hi thanks for this but it isn't what i was looking for..this code seems to automatically colour the cells without any input?..is 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:

report
Usmcjcr Mar 28, 2010 2:09 PM GMT

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 usmc_jcr@yahoo.com

15 pointsBadges:

report

carlosdl Mar 29, 2010 2:25 PM GMT

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

84,495 pointsBadges: