Conditional Formatting in VB

[rmartinez]

Hi Everyone,

I have a question about visual basic in excel. I have a range of cells that I want highlighted a different color depending on their value. I tried doing this with the conditional formatting however, excel only lets you go up 3 values. Is there a code that I can put in the excel visual basic so that it colors the ranges of cell. The following is what I want it to do.

Cells A1:A20 are the range of cells that I want highlighted.
Possible Values are:
Yes
No
N/A
Maybe
Tentative

So if any cell in the range of A1:A20 is equal to Yes then I want it to be highlighted red, if its equal to No then highlight green, if N/A then highlight yellow, if maybe then highlight blue, and if tentative then highlight gray.

I need this for a presentation for work, so if anyone knows a code, I would really appreciate it. A code I was trying was something like:

If worksheets ("Sheet6"). Range("A1:A20") = maybe then
interior.color = RGB
end if

However, I don't know much about visual basic and this code does not work. If anyone could help, I would really appreciate it.

Thanks,
Rmartinez

 

[shades]

Yes, it can be done rather easily using Select Case. I can post an example code tomorrow which allows automatic updates (worksheet code), and background sheet to change the colors (if necessary) without adjusting the VBA code.
________
Vaporizer Affiliates

 

[unmarkedhelicopter]

As Shades suggests, this is possible, and there are a couple of ways to do it, one has a lookup table to take the required condition and the ColourIndex you wish that condition to change to. Although in theory you can display 65k colours in Excel, in practice it's limited to a (adimittedly variable) pallet of 40 (I'd ALWAYS recommend sticking to the standard pallet unless you have a really compelling reason). Humans like colours to simplyfy information transfer and aid descision making but in truth for the above reasons you need to keep your pallet selection (for colour conditions) down to about 10 max as otherwise you start saying "is that the light yellow or just yellow ?"

The code for all the above does slow your sheet down however (as does conditional formatting in general) and most codes will not respond if the value changes in a formula, in this case you need the actual changing cell to be in the range for the check trigger.

The limits on standard conditional formats have been so whinged about that MS has added 'multiple conditional format options' to Excel 2007, but then what happens to anyone receiving a 2007 file when they have plain old 2000 ? The viewers (MS released) may help but I'm not sure. Anyone got any definative info on this ?

Hope you won't need all this but forwarned is forarmed !

 

[shades]

Regarding the 2007 issue and transferring files, I think the only solution is VBA. I will say that the code I will post later never did slow down my spreadsheets (about 30 worksheets, four of which used the worksheet code).
________
Honda Pilot (ATV) specifications

 

[rmartinez]

Thank you guys,

I don't think anyone will be using the 2007 version, so that should not be a problem, since where I work, we are still using the 2003 version. I just need a sample code of what it will look like, as I have no idea how to set it up. I tried using the help menu in excel, however, it is of no help to me, and I still don't know how the code should look. So if you are able to post a sample code of what it should be I would be so grateful.

Thank you very much!!!

 

[shades]

This code goes into the code for worksheet where you want conditional formatting ("Work" on attachment). Right click on the worksheet name and choose "View Code". In the resulting VBE window (on the right side) at the left top make sure that the dropdown is "Worksheet" and top right dropdown is "Change".

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..
    Dim lRowCount As Long
    Dim lColCount As Long
    Dim ptrCellCheck As Range
    Dim ptrValuCheck As Range
    Dim rngCheck As Range
    Set rngCheck = Intersect(Target, Range("E3:H7"))
    If rngCheck Is Nothing Then
        Exit Sub
    Else
        For lRowCount = 3 To 7
            For lColCount = 5 To 8
                Set ptrCellCheck = Cells(lRowCount, lColCount)
                For Each ptrCellCheck In rngCheck
                    ' 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).

                    Select Case ptrCellCheck.Value
                    Case Is = 1
                        Set ptrValuCheck = Cells(lRowCount, 3)
                        ptrCellCheck.Font.ColorIndex = _
                        Application.WorksheetFunction.VLookup(ptrCellCheck.Value, _
                                                              ThisWorkbook.Sheets("CFControl").Range("rngColors"), 2, False)
                    Case Is = 0
                        If Err.Number <> 0 Then
                            ptrCellCheck.Font.ColorIndex = xlNone
                        End If
              Next lColCount
         Next lRowCount
     End If
End Sub

This code assumes that you have a worksheet in Excel named CFControl which lists the names in column A and the color palette number in column B.

Once you have tested this, then you can hide the CFControl worksheet.
________
VAUXHALL CAVALIER

 

[rmartinez]

Thank you for providing the code. I really appreciated it.