Conditional Formatting Help

[RMG]

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;

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

 

[SydneyGeek]

Conditional formatting deosn't cut it with more than 3 options (as you have found) so here's an alternative.

1. Create a 2-column table called ColorTable, on a separate sheet. First column is the month names, second column is the ColorIndex colours that you want to use.

2. Paste this code in a new module (Alt+F11, Insert > Module, Paste, then Alt+Q to return to Excel)

3. Run the code by pressing Alt+F8 and double-clicking the macro name. The code currently uses values from O3 down, checking the month values in column N.

Sub RangeColor()
    Dim lngColor As Long
    Dim c As Range
    
    For Each c In Range("O3:O" & Range("O65536").End(xlUp).Row)
        If c.Value > 1 Then
            lngColor = WorksheetFunction.VLookup(c.Offset(0, -1), Range("colortable"), 2, False)
        Else
            lngColor = xlNone
        End If
        c.EntireRow.Interior.ColorIndex = lngColor
    Next c
End Sub

Denis

 

[RMG]

Thanks for the reply denis,

I have created a new sheet "sheet1" and Column A has months Jan:Dec and Column B has the colour values "3,4,33,etc" I have selected the the data and gone to 'Insert -> Name -> Define' and defined as 'colortable'

i have put data into column o and it does not turn red?

I think it may be something i am doing with the table?

Thanks

 

[SydneyGeek]

No, you need to run the code by pressing Alt+F8 like I said.

If you want the code to trigger when you change a cell, I need to know which cells will be the triggers: Column N? Column O?

Denis