EZ ??? - Conditional Formatting

M

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
 
B

Bernie Deitrick

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
 
B

Bernie Deitrick

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
 
T

Tom Ogilvy

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

Dick Kusleika

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"
Click to expand...

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
 
M

mvyvoda

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
 
M

mvyvoda

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
 
M

mvyvoda

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
 
B

Bernie Deitrick

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
 
M

mvyvoda

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
 
B

Bernie Deitrick

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
 
T

Tom Ogilvy

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)
 
M

mvyvoda

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
 
B

Bernie Deitrick

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
 
M

mvyvoda

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

Cheers,
Mark
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conditional Formatting Question 2
Specific Conditional Selection 1
Conditional Formatting 2
4 conditional formats 2
Conditional Format overwrighting previous conditional format 2
macro problem 8
Highlight Row Based On Condition-So close 3
HELP --- 1004 Application-defined or object-defined erro 3

Top