Formatting Cells Dependent on Contents
 

Danny wrote:
Is their an easy piece of code which could be used on an
entire worksheet to format the cells dependent on their
content. I.e if a cell contains the text 'house' then the
cell has a red background etc. for about 50 conditions.


First you need a procedure to do the colouring.
Maybe something like this:

Sub ColourCells(R As Range)
Dim C As Range
For Each C In R.Cells
Select Case TypeName(C.Value)
Case "Error"
C.Interior.ColorIndex = 1 ' black!
Case "Empty"
C.Interior.ColorIndex = xlNone
Case "Double", "Boolean", "Date"
' a number etc.
C.Interior.ColorIndex = xlNone
Case "String"
Select Case C.Value
Case "House"
C.Interior.ColorIndex = 3 ' red
Case "Car"
C.Interior.ColorIndex = 4 ' red
'etc.
Case Else
C.Interior.ColorIndex = xlNone
End Select
End Select
End Sub

You could test it by selecting some cells and running macro Test:

Sub Test()
ColourCells Selection
End Sub

Then in the module behind the worksheet concerned

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
ColourCells Target
End Sub

This assumes that none of the cells to be coloured are a result of
formulas - if they are then you also need:

Private Sub Worksheet_Calculate()
ColourCells Me.UsedRange.SpecialCells(xlFormulas)
End Sub


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup