Simon336697
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
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
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
try this:
Private Sub Workbook_SheetChange(ByVal
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.Co lor = vbRed
'Sheet1.Columns(1).Interio r.Color = vbRed <- if you want the colomn instead of the row
Case "Green"
Sheet1.Rows(1).Interior.Co lor = vbGreen
Case "Blue"
Sheet1.Rows(1).Interior.Co lor = vbBlue
Case "Yellow"
Sheet1.Rows(1).Interior.Co lor = vbYellow
End Select
End Sub
Private Sub cmbColors_Change()
Select Case cmbColors.Value
Case "Red"
Sheet1.Rows(1).Interior.Co
'Sheet1.Columns(1).Interio
Case "Green"
Sheet1.Rows(1).Interior.Co
Case "Blue"
Sheet1.Rows(1).Interior.Co
Case "Yellow"
Sheet1.Rows(1).Interior.Co
End Select
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Rajesh and Jeremy.
Simon
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
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.Co lor = RGB(0, 0, 0)
For the hex type '&H' the the hex value
Sheet1.Rows(1).Interior.Co lor = &HFF4040
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.Co
For the hex type '&H' the the hex value
Sheet1.Rows(1).Interior.Co
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
Private Sub Workbook_SheetChange(ByVal
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
Try this
Private Sub Workbook_SheetChange(ByVal
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
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
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.WorksheetFunct ion.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
Private Sub Workbook_SheetChange(ByVal
For Each c In Target
If Application.WorksheetFunct
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
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.WorksheetFunct ion.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
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
Dim c As Range
For Each c In Target
If c.Value = "" Then c.Interior.ColorIndex = xlColorIndexNone '<- returns to 'no color'
If Application.WorksheetFunct
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
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.WorksheetFunct ion.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
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
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.WorksheetFunct
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
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
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
ASKER
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
If Sh.Name = Sheets(1).Name Then Sheets(1).Rows _
("2:" & ActiveSheet.UsedRange.Rows
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"
'Target.EntireRow.Copy .UsedRange.Rows(.UsedRange
'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