Link to home
Start Free TrialLog in
Avatar of Simon336697
Simon336697Flag for Australia

asked on

Conditional Formatting Limit to 3 conditions?

Hi guys,

I have 6 columns in a worksheet.

In col A, I have set up an in cell combo box with these values:

Miscellaneous,POS Build,Purhcase,Sent to Store,Sent to Vendor

5 options to select.

What I want to be able to do is:

Upon selection,
Color that row a different color.

I have tried condional formatting like this:

Formula is:  =$A2="POS Build"   --------> format the color red
Formula is:  =$A2="Sent to Store"   --------> format the color yellow
Formula is:  =$A2="Sent to Vendor"   --------> format the color blue

This works, but silly me, I think conditional formatting only allows you to do this for THREE conditions, I cant add any more. Is this the case that you can only have 3 conditions, and if so, is there a way to do this through VBA instead of using the conditional formatting method.

Any help greatly appreciated yet again.

Simon

Avatar of Simon336697
Simon336697
Flag of Australia image

ASKER

Hi guys,

How would I add a select case to the workbook_sheetchange to do the above?

I have at the moment this code.

ThisWorkbook:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = Sheets(1).Name Then Sheets(1).Rows _
("2:" & ActiveSheet.UsedRange.Rows.Count).Sort _
Key1:=Range("F2"), Order1:=xlAscending, Header:=xlGuess
End Sub


In the worksheet called 'Task':

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 And Target.Column = 5 Then  'Check that you've only updated one cell and that it's the right column
      If Target.Value = "y" Then
            'With ThisWorkbook.Sheets("Task") 'Specify your sheet's name here
                'Target.EntireRow.Copy .UsedRange.Rows(.UsedRange.Rows.Count + 1).EntireRow
                'Target.EntireRow.Delete
            'End With
            Call MoveRowsWithYinColumnE
            'Note the Call row works as well, as long as you comment out
            'With ThisWorkbook through to and including Target.EntireRow.Delete
      End If
End If
End Sub

I wish to still be able to retain the above code, but somehow activate the following, but I cant use conditional formatting because it only limits me to 3 conditions.

Formula is:  =$A2="POS Build"   --------> format the color red
Formula is:  =$A2="Sent to Store"   --------> format the color yellow
Formula is:  =$A2="Sent to Vendor"   --------> format the color blue
Formula is:  =$A2="Miscellaneous"   --------> format the color green

Thanks
Avatar of R_Rajesh
R_Rajesh

Hi Simon,

try this:


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
For Each c In Target
If c.Value = "Miscellaneous" Then c.Interior.Color = vbGreen
If c.Value = "Purhcase" Then c.Interior.Color = vbRed
If c.Value = "POS Build" Then c.Interior.Color = vbMagenta
If c.Value = "Sent to Store" Then c.Interior.Color = vbYellow
If c.Value = "Sent to Vendor" Then c.Interior.Color = vbCyan
Next c
End Sub

Rajesh
Or this:

Private Sub cmbColors_Change()
Select Case cmbColors.Value
  Case "Red"
    Sheet1.Rows(1).Interior.Color = vbRed
    'Sheet1.Columns(1).Interior.Color = vbRed <- if you want the colomn instead of the row
  Case "Green"
    Sheet1.Rows(1).Interior.Color = vbGreen
  Case "Blue"
    Sheet1.Rows(1).Interior.Color = vbBlue
  Case "Yellow"
    Sheet1.Rows(1).Interior.Color = vbYellow
End Select
End Sub
ASKER CERTIFIED SOLUTION
Avatar of R_Rajesh
R_Rajesh

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks Rajesh and Jeremy.

Simon
does anyone know where i can get a complete list of color codes?  specifically, i am looking to use the above code but don't know the color code for aqua-blue.

brian
Here are a few lists:

http://eies.njit.edu/~kevin/rgb.txt.html

http://www.pitt.edu/~nisg/cis/web/cgi/rgb.html

To use the RGB format:

Sheet1.Rows(1).Interior.Color = RGB(0, 0, 0)

For the hex type '&H' the the hex value

Sheet1.Rows(1).Interior.Color = &HFF4040
thanks jeremy!  those lists were very helpful... brian
one more question... when inserting the following code (see below) once one of the 4 values has been selected the cell permenately changes colors, even if the cell value is deleted (i.e., goes from "Miscellaneous" to "").  any idea how to get the last conditional format used on the cell to be undone once the cell contents are deleted? ... brian


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
For Each c In Target
If c.Value = "Miscellaneous" Then c.Interior.Color = vbGreen
If c.Value = "Purhcase" Then c.Interior.Color = vbRed
If c.Value = "POS Build" Then c.Interior.Color = vbMagenta
If c.Value = "Sent to Store" Then c.Interior.Color = vbYellow
If c.Value = "Sent to Vendor" Then c.Interior.Color = vbCyan
Next c
End Sub
Hi

Try this

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
For Each c In Target
If c.Value = "Miscellaneous" Then c.Interior.Color = vbGreen
If c.Value = "Purhcase" Then c.Interior.Color = vbRed
If c.Value = "POS Build" Then c.Interior.Color = vbMagenta
If c.Value = "Sent to Store" Then c.Interior.Color = vbYellow
If c.Value = "Sent to Vendor" Then c.Interior.Color = vbCyan
If c.Value = "" Then c.Interior.ColorIndex = -4142  <- returns to 'no color'
Next c
End Sub
hi jeremy.  i tried your suggesting but it doesn't seem to work.  the cell format still remains even after the contents have been deleted.  additionally, when columns are inserted between formatted cells, the new rows inherit the formats of the cells in their corresponding rows, even though no data is present.  how do i avoid this?

brian
How are you clearing the contents? I have tried hightlighting and backspace, right-click clear contents, Delete Key, hightlight right-click and cut. All work fine for me.

As for the insert column problem... it does not raising the sheetchange event when you insert a colomn. Ill keep working on it though
i'm using the delete key to clear the contents... below is my code with your line inserted.  did i do something wrong???

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
For Each c In Target
If Application.WorksheetFunction.IsText(c.Value) Then
Select Case c.Value
Case "B"
c.Interior.Color = RGB(0, 191, 255)
Case "G"
c.Interior.Color = vbGreen
Case "R"
c.Interior.Color = vbRed
Case "Y"
c.Interior.Color = vbYellow
Case ""
c.Interior.ColorIndex = -4142  '<- returns to 'no color'
End Select
End If
Next c
End Sub
Avatar of byundt
Brian,
Try the following changes to your sub. I added a test for a blank cell up front to remove the pattern color if blank. I also changed the Case "" to Case Else--which is more general. After modification, blanking the contents will uncolor the cell.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim c As Range
For Each c In Target
    If c.Value = "" Then c.Interior.ColorIndex = xlColorIndexNone    '<- returns to 'no color'
    If Application.WorksheetFunction.IsText(c.Value) = True Then
        Select Case UCase(c.Value)
        Case "B"
            c.Interior.Color = RGB(0, 191, 255)
        Case "G"
            c.Interior.Color = vbGreen
        Case "R"
            c.Interior.Color = vbRed
        Case "Y"
            c.Interior.Color = vbYellow
        Case Else
            c.Interior.ColorIndex = xlColorIndexNone  '<- returns to 'no color'
        End Select
    End If
Next c
End Sub

Brad
thanks!  the code works but screws up some of my formatting in other cells that don't have actual cell values due to the case else.  how do i limit the above logic to only a certain section of a worksheet to avoid this problem?  in my case the range is AA8:AS27.  also, any thoughts on how to prevent the action of inserting columns in the above range from inheriting the formatting of the cells adjacent to them?  if i insert a column between two cells with a value of "G" the inserted column is green even though it is really blank.

since you're working pro bono here, answer this and i'll throw some bonus points your way.  :)

brian
Brian,
Try the following sub. It doesn't solve the problem with inserted rows inheriting the highlight color, but does restrict the highlighting to the desired range.

WorkbookSheet_Change subs normally apply to any worksheet in the workbook. If you only want it to apply to one worksheet, then change it to Private Sub Worksheet_Change(ByVal Target As Range) and paste the code in the code pane for that worksheet.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim c As Range, targ As Range
Set targ = Intersect(Target, Range("AA8: AS27"))
If Sh.Name <> "Sheet4" Then Exit Sub    'Delete this line if you want code to run on any worksheet
If targ Is Nothing Then Exit Sub    'Code only applies to cells in AA8:AS27
For Each c In targ.Cells
    If c.Value = "" Then c.Interior.ColorIndex = xlColorIndexNone
    If Application.WorksheetFunction.IsText(c.Value) = True Then
        Select Case UCase(c.Value)
        Case "B"
            c.Interior.Color = RGB(0, 191, 255)
        Case "G"
            c.Interior.Color = vbGreen
        Case "R"
            c.Interior.Color = vbRed
        Case "Y"
            c.Interior.Color = vbYellow
        Case Else
            c.Interior.ColorIndex = xlColorIndexNone  '<- returns to 'no color'
        End Select
    End If
Next c
End Sub

Brad
thanks brad... as promised.  i did notice that if i change an existing value from somewhere in the target range (let's say i change a "G" to "B") that once the spreadsheet recalculates to change the color the undo no longer works... do you know why or know how to re-enable it?

brian

https://www.experts-exchange.com/questions/20862484/Points-for-byundt-re-Question-20834777.html
Brian,
Running a macro clears the Undo stack for the worksheet. That's why the Undo stops working.

You can write your own VBA Undo macro. To support it, you would copy the worksheet (or at least the range you care about) at the beginning of the Workbook_SheetChange macro. Saving the entire worksheet would slow the user interface down, obviously. If saving just a specific range, you would use a Public Variant to hold the values. These values would then get restored when you run your Undo macro.
Brad