Using VB to create "conditional formatting"
Using VBA.
Add a standard code module and paste the following:
Option Explicit
Enum eColors
Yellow = 10092543
Green = 13434828
Blue = 16777164
Grey = 12632256
Orange = 10079487
End Enum
Sub MyConditionalFormatting(Target As Range)
Dim cell As Range
Dim clr As Long
For Each cell In Target
Select Case UCase(Trim(cell.Value))
Case "LUNCH": clr = eColors.Yellow
Case "OFF": clr = eColors.Blue
Case "HOLIDAY": clr = eColors.Grey
Case "COURSE": clr = eColors.Orange
Case Else: clr = eColors.Green
End Select
cell.Interior.Color = clr
Next
End Sub
Sub test()
MyConditionalFormatting Range("MyData")
End Sub
To test
On a sheet, select a range, name it "MyData" type some values into some
cells in th erange randomly, include: Holiday, Lunch, Off, Course
run the Test procedure. This passes the range "MyData" to the procedure that
then does the formatting.
This example allows four colors, but you can see that its quite easy to
adapt to use more colors.
HTH
Patrick Molloy
Microaoft Excel MVP
"Fleone" wrote:
I would like to have cells in a workbook formatted depending on their
contents, not their formulas.
For example: If a cell contains the word "Lunch" I want the cell to be
formatted in Yellow, all through the workbook.
Transversely, if a cell does not contain the word "Off", I would like it to
be Blue, all through the workbook.
I have little practical Visual Basic experience and most of what I have
already picked up has been from reverse engineering other scripts.
Would appreciate any assistance.
Thanks.
|