Conditional Formatting Help

RMG

New Member
Joined
Sep 10, 2007
Messages
2
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
ABCDEFGHIJKLMNO
1TYPEDESCRIPTONCOLOURSSMLXL2XL3XLTOTALQTYWHSLRRPDELIVERYDATETOTAL$
2TSHIRTHENLEYTSHIRTBLACK0$57.39$119.95MARCH$-
3WHITE0$57.39$119.95MARCH$-
4SHORTS3/4SHORTBLUE0$40.65$84.95MARCH$-
5RED0$40.65$84.95MARCH$-
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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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.

Code:
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

New Member
Joined
Sep 10, 2007
Messages
2
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,525
Messages
5,602,177
Members
414,510
Latest member
mande358

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top