Multiple Conditional Formatting in VB

[Maver1ck666]

Hi there,

I need to add a set of formats to a column but based on IF statements. For
example, I have column A (Date Raised) and column I (Date Acknowledged), I
want to be able to colour the cells based on what date was added to column I
e.g:

If I <= (A+2) then cell is green
If I > (A+2) then cell is red with white text
If I is null then ignore

Can someone help with some code please.

Thanks in advance!!!

Mav

 

[Mike H]

Hi,

Select the data in column I and try this

Format|Conditional Format
Select formula is
enter the formula

=AND(I1<>"",I1<=A1+2)

Click Format|Patterns and select green

=AND(I1<>"",I1>A1+2)

and select red with white text

Mike
Click Add
Repeat the above but use the formula

 

[Maver1ck666]

Hi Mike,

Thanks for that but I really need to know how to do this in VB as I have
about 6 different conditions I need. Sorry, I thought I had put that in but
obviously not.

Mav

 

[Mike H]

Hi,

This works on the Range i1 to I 20 so change to suit

Sub sonic()
Sheets("Sheet1").Range("I1:I20").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(I1<>"""",I1<=A1+2)"
Selection.FormatConditions(1).Interior.ColorIndex = 4
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(I1<>"""",I1>A1+2)"
Selection.FormatConditions(2).Interior.ColorIndex = 3
Range("I1").Select
End Sub

Mike

 

[Mike H]

Hi,

I forgot the white font so add this to the second condition

Selection.FormatConditions(1).Font.ColorIndex = 2

 

[Maver1ck666]

Sorry Mike,

I think im going mad.

I have copied your code but it doesn't seem to update the cells. Any ideas
please?

Mav

 

[Mike H]

I wrote it so it could go in as worksheet code or in a module so I would
suggest you right click the sheet, view code and paste this in and try and
run it again. I've added another line so I suggest you use this version.

When you say cells aren't changing that may suggest that what you think are
dates may not be so check they are. For a date in A1 type =a1+1 in another
cell and see what you get, it should add 1 day to the date in a1 provided the
date is really a date.

lastly select I1 and click format|Confitional format and check if the macro
has entered the formats etc. If it has then It's fairly certain you hava a
data type issue.

Sub sonic()
Sheets("Sheet1").Range("I1:I20").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(I1<>"""",I1<=A1+2)"
Selection.FormatConditions(1).Interior.ColorIndex = 4
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(I1<>"""",I1>A1+2)"
Selection.FormatConditions(2).Interior.ColorIndex = 3
Selection.FormatConditions(1).Font.ColorIndex = 2
Range("I1").Select
End Sub


Mike

 

[Anant.Basant]

Hi Mike,

Thanks for that but I really need to know how to do this in VB as I have
about 6 different conditions I need. Sorry, I thought I had put that in but
obviously not.

Mav









- Show quoted text -

Hi,

If you are using a version less than Excel 2007 then you can't have
more than 4 conditions (1 default and 3 defined) in Conditional
Formatting.

Anant