G'day,
Im having difficulties with Conditional formatting, i have tried looking through previous posts and while some where helpful i wasn't able to get what i needed. If using Conditional formatting i can achieve what is needed, however i need more than 3 conditions(12 are needed, one for each month of the year), so i need some help with VB code.
The working conditional format formula is below(I Went into the macro and have grabbed the code):
Range("A13:O310").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($O13>1,$N13=""JANUARY"")"
Selection.FormatConditions(1).Interior.ColorIndex = 3
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($O13>1,$N13=""FEBRUARY"")"
Selection.FormatConditions(2).Interior.ColorIndex = 4
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($O13>1,$N13=""MARCH"")"
Selection.FormatConditions(3).Interior.ColorIndex = 33
I have referenced a sample sheet below;
I am trying to get something along the lines of:
IF cell O2 > "1" AND Cell N2 = "January" then the background colour of the entire row = RED
IF cell O2 > 1 AND Cell N2 = February then the background colour of the entire row = Blue
A different colour would be applied for each month, and this code would be applied to quite alot of rows(lets say approx 1000 rows(200 rows on 5 different sheets))
Thanks for any help in advance
Im having difficulties with Conditional formatting, i have tried looking through previous posts and while some where helpful i wasn't able to get what i needed. If using Conditional formatting i can achieve what is needed, however i need more than 3 conditions(12 are needed, one for each month of the year), so i need some help with VB code.
The working conditional format formula is below(I Went into the macro and have grabbed the code):
Range("A13:O310").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($O13>1,$N13=""JANUARY"")"
Selection.FormatConditions(1).Interior.ColorIndex = 3
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($O13>1,$N13=""FEBRUARY"")"
Selection.FormatConditions(2).Interior.ColorIndex = 4
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($O13>1,$N13=""MARCH"")"
Selection.FormatConditions(3).Interior.ColorIndex = 33
I have referenced a sample sheet below;
Final.xls | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
1 | TYPE | DESCRIPTON | COLOURS | S | M | L | XL | 2XL | 3XL | TOTALQTY | WHSL | RRP | DELIVERYDATE | TOTAL$ | |||
2 | TSHIRT | HENLEYTSHIRT | BLACK | 0 | $57.39 | $119.95 | MARCH | $- | |||||||||
3 | WHITE | 0 | $57.39 | $119.95 | MARCH | $- | |||||||||||
4 | SHORTS | 3/4SHORT | BLUE | 0 | $40.65 | $84.95 | MARCH | $- | |||||||||
5 | RED | 0 | $40.65 | $84.95 | MARCH | $- | |||||||||||
Sheet1 |
I am trying to get something along the lines of:
IF cell O2 > "1" AND Cell N2 = "January" then the background colour of the entire row = RED
IF cell O2 > 1 AND Cell N2 = February then the background colour of the entire row = Blue
A different colour would be applied for each month, and this code would be applied to quite alot of rows(lets say approx 1000 rows(200 rows on 5 different sheets))
Thanks for any help in advance