Conditional formatting on multiple criteria

Solved/Closed
pramvyas000 Posts 4 Registration date Tuesday November 17, 2015 Status Member Last seen November 20, 2015 - Nov 18, 2015 at 12:27 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - 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

2 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Nov 19, 2015 at 11:26 AM
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
pramvyas000 Posts 4 Registration date Tuesday November 17, 2015 Status Member Last seen November 20, 2015
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 Posts 4 Registration date Tuesday November 17, 2015 Status Member Last seen November 20, 2015
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