How to formulate the following.....


Posted by Steve on November 28, 2000 7:47 PM

Greetings
here goes,

Cell B6 = value1
Cell B7 = value2

If B7 is less than B6 then the font (or background color) of B7 equals red.

If B7 is greater than B6 then the font (or background color) of B7 equals blue.

If B7 contains no value then nothing is done.

This is a simplified example of what I am trying to do. No need to get confused with facts and figures. =P

Anyone have an answer for this? I'm new and stumped.

Thanks
Steve



Posted by Ben O. on November 28, 2000 8:34 PM

You should be able to do what you want fairly easily with Conditional Formatting. Just select the cell(s) you want to apply the formatting to, and select Conditinal Formatting from the Format menu. From there it's self-explanatory.

If you're looking for VBA code, this should do the trick:

Sub Macro1()
With Range("B7")
.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=$B$6"
.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=$B$6"
.FormatConditions(1).Font.ColorIndex = 3
.FormatConditions(2).Font.ColorIndex = 5
End With
End Sub


-Ben