Anyone know if there's any way to get more than three conditional formatting applied to a given cell?
There are a couple of ways to do this. One is while you are limited to three conditionals you could do more then this by using helper columns. Personally I find it easier to do it with VBA but with a slight performance penalty depending on how many cells, etc. are involved.
Here is an example:
Code:
Status = Workbooks(wb).Worksheets(LogSheetName).Cells(wsDaily, 10).Value
Workbooks(wb).Worksheets("Forms").Cells(z, 75) = Status
In this example I am picking up the value for the variable 'Status' from one of 31 sheets from a particular cells and transferring that to the worksheet called 'Forms' at row z, column 75.
I then test for 'Status' and based on what 'Status' is I will color a cell in the same row in column 2, and possibly part of the entire row as well.
Note the following if/then combinations may be better as case/selects, I'll figure that out later, this works now. In some cases you may need to remember the previous color. For example you select a row and you want to highlight the selected row. When you move off of that row you may want it to revert back to its previous colors.
Code:
If Status = "" Then
Workbooks(wb).Worksheets("Forms").Range("B" & z, "BI" & z).Interior.ColorIndex = 15
Else
Workbooks(wb).Worksheets("Forms").Range("B" & z, "BI" & z).Interior.ColorIndex = 0
Workbooks(wb).Worksheets("Forms").Range("D" & z, "BI" & z).Font.Strikethrough = False
Workbooks(wb).Worksheets("Forms").Range("D" & z, "BI" & z).Font.ColorIndex = 0
End If
If UCase(Status) = "V" Then
Workbooks(wb).Worksheets("Forms").Range("B" & z).Interior.ColorIndex = 4
Workbooks(wb).Worksheets("Forms").Range("D" & z, "BI" & z).Font.Strikethrough = True
Workbooks(wb).Worksheets("Forms").Range("D" & z, "BI" & z).Font.ColorIndex = 3
End If
If UCase(Status) = "C" Then
Workbooks(wb).Worksheets("Forms").Range("B" & z).Interior.ColorIndex = 4
Else
If UCase(Status) = "NC" Then
Workbooks(wb).Worksheets("Forms").Range("B" & z).Interior.ColorIndex = 6
End If
End If
Basically if 'Status'="" then that indicates data not yet imported, we color that gray, else we color the line white. If 'Status' = "V" we have a set of data that has been voided, we color the line white to change whatever it was previously, change the font to red, and then added strikethrough.
In this way we basically have an unlimited number of conditional formats possible with about 40 or so different colors available (some of the color codes end up being basically the same).
The next trick is to determine what triggers the macro. In this example it is triggered on a sheet change taking place on another sheet. If a sheet change takes place on another sheet (which is the change in 'Status') then a flag is set indicating it took place and the macro cycles through all possible sheets looking at the status and transfers the status to the main sheet called 'Forms'. During this same cycle I do the color changes.
On the other hand I could do the color changes when triggered off a a sheet change event in the sheet 'Forms'. What you use should be based on your particular situation. In my case I may go this route since I am considering allowing this to be a two way street, that is the ability of manually changing the status on my 'Forms' sheet and transferring that automagically to the other sheets while still allowing the current method.
One thing to keep in mind is sheet protection. When protecting a sheet you may need to select the checkbox for 'allow formatting'.
Perry
