Previous Topic Next Topic

Conditional formatting with 4 IF statements

Closed
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 -
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:)

4 replies

Answer 1 / 4

plz tell me how to add more than three conditon in excel......
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
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
Answer 2 / 4

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
Answer 3 / 4

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
Answer 4 / 4
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
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
Ask a question