format cell based on contents using VB

15 pts.
Tags:
Conditional statements
Excel VBA
VBA
Visual Basic for Applications
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
UPDATED: March 29, 2010  2:25 PM

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:

<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

 
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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • 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?..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
    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
    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
    65,110 pointsBadges:
    report

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following