Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601

    Unanswered: Conditional Format

    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.
    Ryan
    My Blog

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1

    Code Conditional Formatting

    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

  3. #3
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    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...
    Ryan
    My Blog

  4. #4
    Join Date
    Jun 2006
    Posts
    103
    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.

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

  6. #6
    Join Date
    Oct 2003
    Posts
    1,091
    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
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  7. #7
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    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...
    Ryan
    My Blog

  8. #8
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

  9. #9
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    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
    Last edited by rguy84; 08-29-06 at 14:23.
    Ryan
    My Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •