WOW... it works. I can't thank you (and everyone else, Tom & Dick... harry?)
enough.

Cheers,
Mark

"Bernie Deitrick" wrote:

Try the macro version 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("Q:Q")

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(DATEVALUE(RIGHT(R1,2) & ""/1/"" & " & _
"LEFT(R1,4))=DATEVALUE(""" & s_Current & """)," & _
"DATEVALUE(RIGHT(R1,2) & ""/1/"" & LEFT(R1,4))" & _
"<=DATEVALUE(""" & s_Future & """)," & _
r_TData.Cells(1, 1).Address(False, False) & "=""IN"")"
.FormatConditions(1).Interior.ColorIndex = 7
End With

End Sub

"mvyvoda" wrote in message
...
All... the code runs perfectly, but doesn't color code anything for some
reason (sounds like a formatting error). I have this piece of code I need:

s_current = Format(DateSerial(Year(Date), Month(Date), 1), "yyyy-mm")
s_future = Format(DateSerial(Year(Date), Month(Date) + 1, 1), "yyyy-mm")

I use this because "R" has to be formatted strings with examples being:

2006-08
2007-05
2005-09, etc.

Does that help? Do you need more example. I can send anything anwhere.

Thanks so much!,
-m

"Bernie Deitrick" wrote:

Should be an easy fix, depending on their form (Like, do they include month, day and year?)....
Try
the macro below. If that doesn't work, post some sample data.....

HTH,
Bernie
MS Excel MVP

Sub ApplyCFMacro()
Dim s_Current As String
Dim s_Future As String
Dim r_TData As Range
Set r_TData = Range("Q:Q")

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(DATEVALUE(R1)=DATEVALUE(""" & s_Current & """)," & _
"DATEVALUE(R1)<=DATEVALUE(""" & s_Future & """)," & _
r_TData.Cells(1, 1).Address(False, False) & "=""IN"")"
.FormatConditions(1).Interior.ColorIndex = 7
End With

End Sub



"mvyvoda" wrote in message
...
oh yes... they are string, not dates. now what? does DATAVALUE mess that up?

I see what you're saying about column vs. cell.

Thanks,
Mark

"Bernie Deitrick" wrote:

It could be beacause your 'dates' aren't true dates. What happens to the dates when you
change
the
display date format - say from mmm dd to mmm dd, yyyy? If the cells don't show the year after
doing
that, you actually have strings, and we would need to change the formula in the code.

The code does not pertain to R1 only - when you select multiple cells to apply a C.F., Excel
expects
the formula to be written as it applies to the first cell of the selection, and updates it
(similar
to how a formula is copied) as it is applied to other cells within the selection.

HTH,
Bernie
MS Excel MVP


"mvyvoda" wrote in message
...
I ported that code to my application, however it didn't seem to work. Is it
because I need this to work for the entire Column R? Does your code only
pertain to R1 and not the entire Column R?

I really appreciate your help!,
Mark

"Bernie Deitrick" wrote:

Sorry, forgot to change my code when I set r_TData ...

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 & """)," & _
r_TData .Cells(1, 1).Address(False, False) & "=""IN"")"
.FormatConditions(1).Interior.ColorIndex = 7
End With

End Sub


--
HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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