Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting using part of the cells contents | Excel Discussion (Misc queries) | |||
Directing cell contents to one of two cells dependent on its value | Excel Worksheet Functions | |||
directing cell contents dependent on its value | Excel Worksheet Functions | |||
how do I fix formatting mistakes: cells and contents are wavy | Excel Worksheet Functions | |||
Data Validation list dependent on contents of another cell | Excel Discussion (Misc queries) |