Conditional Format

rguy84 · #1 (**permalink**) 08-22-06, 18:03

I was just asked about conditional formatting. I am not an expert at excel at all. I want to color a cell based on a value that is a word. So if the cell says required I want the cell color to be red, green if it says not required. I am hoping that it will do this dynamically, so it will affect current cells plus new values entered.

I have tried to do it several ways, but no luck.

MikeTheBike · #2 (**permalink**) 08-23-06, 08:17

Hi rguy84

Heres some code I wrote a year or so ago, two reason for not using the built in conditional formatting
1) allows more than 3 conditions

2) you do not need to format each cell as the sheet changes

This should be basted in a standard Module

Code:

Sub SetColours(ThisRange As Range)
    Dim cel As Range
    
    For Each cel In ThisRange
        With cel
                ActiveSheet.Unprotect
                
                Select Case Trim(UCase(cel))
                    Case Is = "V", "V/2"
                        .Interior.ColorIndex = 44
                        .Interior.Pattern = xlSolid
                    Case Is = "SC", "SC/2"
                        .Interior.ColorIndex = 35
                        .Interior.Pattern = xlSolid
                    Case Is = "DC"
                        .Interior.ColorIndex = 6
                        .Interior.Pattern = xlSolid
                    Case Is = "DV", "DV/2"
                        .Interior.ColorIndex = 44
                        .Interior.Pattern = xlGray25
                    Case Is = "DSC", "DSC/2"
                        .Interior.ColorIndex = 35
                        .Interior.Pattern = xlGray25
                    Case Is = "DDC"
                        .Interior.ColorIndex = 6
                        .Interior.Pattern = xlGray25
                    Case Else
                        .Interior.ColorIndex = xlNone
                        .Interior.Pattern = xlSolid
                End Select            
                ActiveSheet.Protect
        End With
    Next cel
End Sub

and this code will need to be in every Sheet Module where you want to use the formatting.

Code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    SetColours Target
End Sub

HTH

MTB

rguy84 · #3 (**permalink**) 08-23-06, 19:12

Mike-

I get a few errors when I do that. First if I paste the first chunk into the whole workbook mod, and the second into the sheet mod, it says function undefined. If I put both together it turns read-only...

mkggoh · #4 (**permalink**) 08-24-06, 02:32

Quote:

Originally Posted by rguy84

Mike-

I get a few errors when I do that. First if I paste the first chunk into the whole workbook mod, and the second into the sheet mod, it says function undefined. If I put both together it turns read-only...

try put both chunk of code on the sheet mod

OR

first chunk on module1

but need to use public sub

second chunk on sheet mod.

MikeTheBike · #5 (**permalink**) 08-24-06, 02:41

Hi

The function should be posted in a separate code module (not the 'ThisWorkbook' module) ie. from the menu Insert -> Module and paste in there.

You will need to call the function rom each sheet that needs the formating.

Let us know.

MTB

shades · #6 (**permalink**) 08-24-06, 21:18

Or another approach:

I have a "CFControl" worksheet that is hidden, which contains the named range: rngcolors defined this way (or could be dynamic named range):

=CFControl!$A$2:$B$87

With the worksheet active that you want the formatting, then right-click the tab and choose "View Code". This brings up the VBE window, with the module on the right. Paste the following code into the window.

The code then uses that hidden worksheet as a reference to know which color to use. I went this route because as you can see I had 86 possible conditions. This code automatically handles all 86 automatically adding the color. If I need to change the colors, I do so on the hidden worksheet ("CFControl") rather than doing anything with the code. Adjust your range on the active worksheet as needed

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
' Conditional Formatting for more than 3 conditions
    Dim rng As Range
    ' Target is a range::therefore,it can be more than one cell
    ' For example,,someone could delete the contents of a range,
    ' or someone could enter an array..
    Set rng = Intersect(Target, Range("P2:Y72"))
    If rng Is Nothing Then
        Exit Sub
    Else
        Dim cl As Range
        For Each cl In rng
            On Error Resume Next
            ' — The preceding line doesn ’t change the cell ’s background
            ' — color if the cell ’s value is not found in the range
            ' — that we specified ((rngcolors).
            cl.Font.ColorIndex = _
            Application.WorksheetFunction.VLookup(cl.Value, _
                ThisWorkbook.Sheets("CFControl").Range("rngColors"), 2, False)
            If Err.Number <> 0 Then
                cl.Font.ColorIndex = xlNone
            End If
        Next cl
    End If
End Sub

rguy84 · #7 (**permalink**) 08-28-06, 17:51

Mike,

I threw the function into a new module. Then put the function call into a sheet. I typed V and it turned an orange color. Then following that I had that read only error. I thought maybe it only let's me type the words you defined...nope read-only...

MikeTheBike · #8 (**permalink**) 08-29-06, 04:43

Hi rguy84

I think the problem is the two code lines
ActiveSheet.Unprotect and ActiveSheet.Protect

Try removing these two lines.

I appologise for this; that's the problem use code extracts without testing them properly.

My origional code was designed to worked with cells in a specific range where the cells were unprotected (but not the sheet). The origional code tested that target cell was in this specific range. Because the cell was unprotected, entering text into a cell did not trigger the read only error, but changing the backgrount colour did, hence the need to unprotect the sheet before changing the colour.

I think that is the cause of the error but, maybe not !?

HTH

MTB

rguy84 · #9 (**permalink**) 08-29-06, 13:02

I caught the unprotect line near the bottom, I'll look at it closer to find the othrer...I'll try that and report back.

On the other hand, is there a chart that I can refer to to adjust the colors?

It now works

Internet Services

Web Development

Digital Marketing

Consumer Tech