Conditional formatting with 4 IF statements

Closed
cyberskatergirl - Sep 24, 2008 at 06:37 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Apr 22, 2010 at 09:53 AM
Hello,

I am trying to create a VBA code that will affect all cells from J2 to BZ1000.

Column I shows the revised milestone date
COlumn G shows the % complete
COlumn F shows the RAG status
Column J to BZ show a calendar (one week per column).

I have 4 conditions:

=IF(J3<$I3,0,IF($G3<1,AND($F3="G",ISNUMBER(J3)),0)) then J3 colour should be green

=IF(J3<$I3,0,IF($G3<1,AND($F3="A",ISNUMBER(J3)),0)) then J3 colour should be amber

=IF(J3<$I3,0,IF($G3<1,AND($F3="R",ISNUMBER(J3)),0)) then J3 colour should be red

=IF G3=100%, then J3 colour should be blue.



Any idea how to repsent this in VBA code please?

Thanks so much!

A:)
Related:

4 responses

plz tell me how to add more than three conditon in excel......
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 8, 2010 at 05:19 PM
You can add up to 7 nested IF (if within if)

=IF(COND1, what to do if COND1true, what to do if COND1 false)

in what to do part, you can add other if clause just as before

=IF(COND1, IF(Cond2, what to do Cond2 true, what to do Cond2 false), what to do if COND1 false)



and so on
Access provides for conditional formatting without the messy CASE SELECT or IF-THEN statements. For an excellent review, look at https://docs.microsoft.com/en-us/previous-versions/office/developer/office-xp/aa139965(v=office.10)?redirectedfrom=MSDN
I am trying to create an If statement using a report that is showing a date as #NUM! or =Date(0,0,0). How to I write this in an if statement to have Excel recognize the date is missing
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 22, 2010 at 09:53 AM
One approach can be

=if(iserror(Date(0,0,0),"",Date(0,0,0))

I dont know how u get 0,0,0 but I left them as you had,. You can replace with right values