VBA conditional formating

munkifisht

New Member
Joined
Mar 17, 2005
Messages
39
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.
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,113,989
Messages
5,545,355
Members
410,679
Latest member
rolandbianco
Top