Conditional Formatting Question

[mvyvoda]

All,

How do I, in VBA, conditional format a column based on another columns data?
For example I have dates in the column (DATE COLUMN) I need formatting,
however I have text in another column (TEXT COLUMN). I want to make the date
columns format dependant on the text column. Here's what I have thus far:

s_current = Format(DateSerial(Year(Date), Month(Date), 1), "yyyy-mm")
s_future = Format(DateSerial(Year(Date), Month(Date) + 2, 1), "yyyy-mm")
Range("R:R").Select 'THIS IS DATE COLUMN
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:=s_current, Formula2:=s_future
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:= TEXT COLUMN DATA <<< ---- NEED HELP HERE, I THINK
Selection.FormatConditions(2).Interior.ColorIndex = 7

I have two conditions in which I need to color code the DATE COLUMN:
1.) in between s_current & s_future
2.) if the value of the corresponding cell in TEXT COLUMN is "IN"

Any help would be much appreciated.

Thanks,
-m

 

[Bill Pfister]

You should try "expression" condiftional formatting instead of "cell value."
This is the way you use conditional formatting to compare values in a cell
that "isn't itself." This sample changes the cond. formatting of cell C1
based on the value of B1.

Regards,
Bill



Dim strFormat As String
Dim strData As String

strFormat = "c1"
strData = "b1"

Range(strFormat).FormatConditions.Delete
Range(strFormat).FormatConditions.Add Type:=xlExpression,
Formula1:="=if( and( " & strData & ">1, " & strData & "<5 ), true, false )"
Range(strFormat).FormatConditions(1).Interior.ColorIndex = 46

 

[Tom Ogilvy]

Question was answered that same day in a later (new) posting of the
question.