VBA conditional formating


New Member
Mar 17, 2005
I have a big sheet with lots of entries and I want to write some code that will change the format of a number of cells automatically when the value of a cell in that row changes. I have it working at the moment with conditional formatting, but that can only handle 3 conditions and I need to have an infinite number.

What I have at present is a list on the sheet which is used as a validation list for the values of row C. The options at present are Done, In Progress, To Do and On Hold, but I will need more in the future. Beside each entry in this list is a cell with a particular background colour, so in effect a key. What I would like to do is use a code that will look at the value of cell C whatever and depending on the value of that cell it will then apply the colour in the key to all relevent cells, and this will happen on all sheets through the entire workbook.I would also liek it to happen automatically and not through a macro. I also need the code to apply bold formatting to a row if the cell in B is filled. I should also say that each key may be different to each sheet.

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying


Sep 3, 2002
This is untested code.

It assumes:

Sheet3 holds text identifiers in column A, colors in B, starting at row #1
All other sheets should be monitored for column C changes

The code goes in the ThisWorkbook module.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_SheetChange(<SPAN style="color:#00007F">ByVal</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)

    <SPAN style="color:#00007F">If</SPAN> Sh.Name = "Sheet3" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Intersect(Target, [c:c]) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">With</SPAN> Application
        .EnableEvents = <SPAN style="color:#00007F">False</SPAN>
        .ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range, Rng <SPAN style="color:#00007F">As</SPAN> Range, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet3")
        <SPAN style="color:#00007F">Set</SPAN> Rng = .Range("a1:b" & .Cells(Rows.Count, "a").End(xlUp).Row)
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Intersect(Target, [c:c])
        <SPAN style="color:#00007F">With</SPAN> Application.WorksheetFunction
            i = 0
            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
            i = .Match(c.Value, Rng.Columns(1), 0)
            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
            <SPAN style="color:#00007F">If</SPAN> i = 0 <SPAN style="color:#00007F">Then</SPAN>
                c.Interior.ColorIndex = xlNone
                c.Font.Bold = <SPAN style="color:#00007F">False</SPAN>
            <SPAN style="color:#00007F">Else</SPAN>
                c.Interior.ColorIndex = Rng.Cells(i, 2).Interior.ColorIndex
                <SPAN style="color:#00007F">If</SPAN> Len(c.Offset(0, -1)) > 0 <SPAN style="color:#00007F">Then</SPAN> c.Font.Bold = <SPAN style="color:#00007F">True</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> c
    <SPAN style="color:#00007F">Set</SPAN> Rng = <SPAN style="color:#00007F">Nothing</SPAN>
    <SPAN style="color:#00007F">With</SPAN> Application
        .EnableEvents = <SPAN style="color:#00007F">True</SPAN>
        .ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
End <SPAN style="color:#00007F">Sub</SPAN></FONT>

Forum statistics

Latest member

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