Conditional formatting on multiple criteria [Solved/Closed]

Report
Posts
4
Registration date
Tuesday November 17, 2015
Status
Member
Last seen
November 20, 2015
-
Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
-
hi,
i am trying to create an excel vba code for creating a conditional formatting where i want to insert colour on the basis of multiple rules. i am new to excel hence finding it difficult to build a code

i have three column
column N : claimed Amount
Column J : Claim status
Column Q : Age

i want to use following conditions
a) if claimed Amount > 500 and < 50000 and claim status DI2,DI3,DI4 and age = 3 months green colour. if age = 4 months then yellow colour and if age is more than 4 months then colour should be red
b) if claimed amount > 50000 and <= 500000 and claim status is DI2 or DI3 or DI4 and age = 6 of 7 month then green colour, age = 8 months then yellow colour and age beyond 8 months then red colour
c) if claimed amount > 500000 and claim status is DI2 or DI3 or DI$ and age = 1 year but less than 15 months green colour, age = 15 months yellow colour and age beyond 15 months red colour.

thank you in advance for your help
pramod

2 replies

Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
448
Hi Pramod,

Have you tried using formula's with conditional formatting?

Formula for A green would look like:
=AND(N2>500,N2<50000,OR(J2="DI2",J2="DI3",J2="DI4"),Q2="3 months")

Create formula's like these for the rest of your conditions.

Best regards,
Trowa
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
4
Registration date
Tuesday November 17, 2015
Status
Member
Last seen
November 20, 2015

Hi Trowa,
since the overall condition would be 9 is it possible to create 9 formulas in conditional formatting?
with Regards
Pramod Vyas
Posts
4
Registration date
Tuesday November 17, 2015
Status
Member
Last seen
November 20, 2015

Hi Trowa,
one more question since the no of rows will be growing very quickly is it possible to have a vba code to perform the function
with Regards
Pramod Vyas
Posts
4
Registration date
Tuesday November 17, 2015
Status
Member
Last seen
November 20, 2015

hi Trowa
Thanks the formula worked and i also learned that no limit is placed on no of rules
thanks for your help
with Regards
Pramod Vyas
Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
448
Hi Pramod,

Good to see you worked it out!

Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!