Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting Cells Dependent on Contents
You are limited to 56 colors in a worksheet.
Add a Change event to the sheets code page - right click on the sheet tab & select View Code Private Sub Worksheet_Change(ByVal Target As Range) Target.Interior.ColorIndex = GetColor(Target.Value) End Sub Now that you're in the IDE, add a standard module with this code: Function GetColor(sText As String) As Long Dim cl As Long On Error Resume Next cl = Application.WorksheetFunction.VLookup(sText, ThisWorkbook.Names("ColorList").RefersToRange, 2, False) Err.Clear On Error GoTo 0 GetColor = cl End Function add another worksheet. in column A add the text list that you want and in B the corresponing index - must be 0 to 56 range name the list ColorList When you enter something on your first sheet, the change event sets the cells color depending on the return value from the function. The event handler passes the cell's new value to the function. The function simply uses the vlookup function to match the text in A and get a numbet from B. Unfortunately the VLookUp function generates an error condition if there's no match, so our code skips this. Simple hey? If anyone wants my workbook, email me directly Patrick Molloy Microsoft Excel MVP -----Original Message----- 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. This is similar to conditional formating but i need more than 3 conditions and the code should update the colour of the cell if the content changes? I am sure this must be fairly simple, but when i have aksed in the past the code i received was far too complex and did not update? I am fairly new to the code side of Excel so please keep any examples simple. Danny. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
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) | |||
Formatting Cells Dependent on Contents | Excel Programming |