EZ ??? - Conditional Formatting
 

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

EZ ??? - Conditional Formatting
 

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

EZ ??? - Conditional Formatting
 

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

EZ ??? - Conditional Formatting
 

Do you know how to do it manually? If so, turn on the macro recorder and do
it manually to get sample code.

--
Regards,
Tom Ogilvy


"mvyvoda" wrote:

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

EZ ??? - Conditional Formatting
 

m

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"


If you're selecting R:R, the R1 is the active cell and any relative formulas
should be written relative to that cell. Here's an example where you select
R1, but don't select the whole column. I couldn't tell from your example if
you were coloring them different or what, so you may need to adjust.

Sub AddFC()

Dim s_current As String, s_future As String

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

Sheet1.Range("R1").Select

With Sheet1.Range("R:R")
With .FormatConditions.Add(xlCellValue, xlBetween, s_current,
s_future)
.Interior.ColorIndex = 3
End With
With .FormatConditions.Add(xlExpression, , "=$E1=""IN""")
.Interior.ColorIndex = 7
End With
End With

End Sub

--
Dick Kusleika
MS MVP - Excel
www.dailydoseofexcel.com

EZ ??? - Conditional Formatting
 

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

EZ ??? - Conditional Formatting
 

Dick, this seems easy enough. I ported this code, however it didn't work out.
Can you advise what the code would be if I needed the entire column
highlighted with those two conditions?

Thanks so much for your time and effort!,
-m

"Dick Kusleika" wrote:

m

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"


If you're selecting R:R, the R1 is the active cell and any relative formulas
should be written relative to that cell. Here's an example where you select
R1, but don't select the whole column. I couldn't tell from your example if
you were coloring them different or what, so you may need to adjust.

Sub AddFC()

Dim s_current As String, s_future As String

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

Sheet1.Range("R1").Select

With Sheet1.Range("R:R")
With .FormatConditions.Add(xlCellValue, xlBetween, s_current,
s_future)
.Interior.ColorIndex = 3
End With
With .FormatConditions.Add(xlExpression, , "=$E1=""IN""")
.Interior.ColorIndex = 7
End With
End With

End Sub

--
Dick Kusleika
MS MVP - Excel
www.dailydoseofexcel.com

EZ ??? - Conditional Formatting
 

Dick... this DOES work, however I need the two conditions ANDED together. I'm
so close on this one but don't know how to AND your two WITH statements
together.

Thanks,
-m

"Dick Kusleika" wrote:

m

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"


If you're selecting R:R, the R1 is the active cell and any relative formulas
should be written relative to that cell. Here's an example where you select
R1, but don't select the whole column. I couldn't tell from your example if
you were coloring them different or what, so you may need to adjust.

Sub AddFC()

Dim s_current As String, s_future As String

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

Sheet1.Range("R1").Select

With Sheet1.Range("R:R")
With .FormatConditions.Add(xlCellValue, xlBetween, s_current,
s_future)
.Interior.ColorIndex = 3
End With
With .FormatConditions.Add(xlExpression, , "=$E1=""IN""")
.Interior.ColorIndex = 7
End With
End With

End Sub

--
Dick Kusleika
MS MVP - Excel
www.dailydoseofexcel.com

EZ ??? - Conditional Formatting
 

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

EZ ??? - Conditional Formatting
 

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

EZ ??? - Conditional Formatting
 

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

EZ ??? - Conditional Formatting
 

Bernie's revised code worked fine for me (and used all three conditions in an
AND statement for a single condition) after I removed the space after r_TData

r_TData .Cells(1, 1).Address(False, False)

becomes

r_TData.Cells(1, 1).Address(False, False)


--
Regards,
Tom Ogilvy

"mvyvoda" wrote:

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

EZ ??? - Conditional Formatting
 

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

EZ ??? - Conditional Formatting
 

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

EZ ??? - Conditional Formatting
 

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