Home > IT Answers > Development > format cell based on contents using VB
Voicetec
15 pts.
0
Q:
format cell based on contents using VB
VBA, Excel VBA, Conditional statements, 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: Jul 14 2009  10:45 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Jade1977
340 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
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
                 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


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.
Last Answered: Jul 14 2009  5:10 PM GMT by Jade1977   340 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Voicetec   15 pts.  |   Jul 15 2009  9:57AM 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?

 
0