Color Coding by Condition

[claire.venturino]

Excel 2000
Is there a way to color-code a row based on a condition of one cell in
that row, but not using Visual Basic Editor? Conditional formatting
would be perfect except I am only allowed 3 conditions. I have a
column containing several different numbers and I am trying to color
code groups of numbers which would require more than 3 conditions.
There are approximately 100 or so different numbers and a total of 8
colors (conditions). I'm not an expert at all in Excel and if Visual
Basic Editor is needed then I would need some step by step guidance
with it. I appreciate any help! Thanks
CV

 

[Bernie Deitrick]

Claire,

Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears. I have written the code so that you only need to modify it slightly to set the
range, but to get different colors you will use a worksheet function on the sheet.

What you do need to do is insert a formula into the cells of column A that will return the desired
color code. To see the various colors, select A2:A57, type =ROW()-1 and press Ctrl-Enter.

Then all you need to do is enter a formula into cells in column A that will return your desired
color code....For example

=IF(AND(B2>3,B2<=4),10,15)

That will format the row using either color 10 or 15....

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Calculate()
Dim myC As Range
For Each myC In Range("A2", Cells(Rows.Count, 1).End(xlUp))
If myC.Value <> "" Then
Cells(myC.Row, 2).Interior.ColorIndex = myC.Value
End If
Next myC
End Sub