Conditional formatting on multiple criteria [Solved/Closed]

pramvyas000 4 Posts Tuesday November 17, 2015Registration date November 20, 2015 Last seen - Nov 18, 2015 at 12:27 AM - Latest reply: TrowaD 2391 Posts Sunday September 12, 2010Registration dateModeratorStatus July 12, 2018 Last seen
- 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 

5 replies

Best answer
TrowaD 2391 Posts Sunday September 12, 2010Registration dateModeratorStatus July 12, 2018 Last seen - Nov 19, 2015 at 11:26 AM
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

Something to say? Add comment

CCM has helped 1706 users this month

pramvyas000 4 Posts Tuesday November 17, 2015Registration date November 20, 2015 Last seen - Nov 19, 2015 at 10:36 PM
Hi Trowa,
since the overall condition would be 9 is it possible to create 9 formulas in conditional formatting?
with Regards
Pramod Vyas
pramvyas000 4 Posts Tuesday November 17, 2015Registration date November 20, 2015 Last seen - Nov 19, 2015 at 10:38 PM
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
pramvyas000 4 Posts Tuesday November 17, 2015Registration date November 20, 2015 Last seen - Nov 20, 2015 at 07:12 AM
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 2391 Posts Sunday September 12, 2010Registration dateModeratorStatus July 12, 2018 Last seen - Nov 23, 2015 at 11:16 AM
Hi Pramod,

Good to see you worked it out!

Best regards,
Trowa