Conditional formatting on multiple criteria [Solved/Closed]

Posts
4
Registration date
Tuesday November 17, 2015
Last seen
November 20, 2015
- - Latest reply: TrowaD
Posts
2435
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 4, 2018
- Nov 23, 2015 at 11:16 AM
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
See more 

2 replies

Best answer
Posts
2435
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 4, 2018
2
Thank you
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

Thank you, TrowaD 2

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

CCM has helped 2561 users this month

pramvyas000
Posts
4
Registration date
Tuesday November 17, 2015
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
pramvyas000
Posts
4
Registration date
Tuesday November 17, 2015
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
Last seen
November 20, 2015
0
Thank you
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
TrowaD
Posts
2435
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 4, 2018
-
Hi Pramod,

Good to see you worked it out!

Best regards,
Trowa