m,

You didn't say what column was your text column.... change as needed in the macro below.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim s_Current As String
Dim s_Future As String
Dim r_TData As Range
Set r_TData = Range("B:B")

s_Current = DateSerial(Year(Date), Month(Date), 1)
s_Future = DateSerial(Year(Date), Month(Date) + 2, 1)
With Range("R:R")
.Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(R1=DATEVALUE(""" & s_Current & """)," & _
"R1<=DATEVALUE(""" & s_Future & """)," & _
Range("TEXTDATA").Cells(1, 1).Address(False, False) & "=""IN"")"
.FormatConditions(1).Interior.ColorIndex = 7
End With

End Sub




"mvyvoda" wrote in message
...
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