Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format colour of cells based on other cell - 34 colours
I need to conditional format the BG colour of a cell based on another cell. I
can deal with 4 colours but not 34 seperate colours. Any ideas? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format colour of cells based on other cell - 34 colours
Hi Graham,
Try something like: '============= Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim rng2 As Range Set rng = Me.Range("B1") '<<==== CHANGE Set rng2 = Me.Range("A1") '<<==== CHANGE Set rng = Intersect(rng, Target) If Not rng Is Nothing Then With rng2 Select Case UCase(rng.Value) Case "ANNE": .Interior.ColorIndex = 3 Case "BEN": .Interior.ColorIndex = 4 Case "CAROL": .Interior.ColorIndex = 5 Case "DAVID": .Interior.ColorIndex = 6 Case "EWAN": .Interior.ColorIndex = 7 Case "FREDA": .Interior.ColorIndex = 8 Case "GRAHAM": .Interior.ColorIndex = 9 Case "HARRY": .Interior.ColorIndex = 10 Case "IAN": .Interior.ColorIndex = 11 Case "JANE": .Interior.ColorIndex = 12 Case "KATE": .Interior.ColorIndex = 13 Case "LEN": .Interior.ColorIndex = 14 Case "MARY": .Interior.ColorIndex = 15 Case "NORA": .Interior.ColorIndex = 16 Case Else: .Interior.ColorIndex = xlNone End Select End With End If End Sub '<<============= As written, the background colour of A1 changes in response to the value inserted in B1. Change the ranges and trigger values to suit. This is worksheet event code and should be pasted into the worksheets's code module (not a standard module and not the workbook's ThisWorkbook module): Right-click the worksheet's tab Select 'View Code' from the menu and paste the code. Alt-F11 to return to Excel. --- Regards, Norman "Graham Tritton" wrote in message ... I need to conditional format the BG colour of a cell based on another cell. I can deal with 4 colours but not 34 seperate colours. Any ideas? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format colour of cells based on other cell - 34 colours
Try this by Bob Philips http://www.developersdex.com/vb/mess...2677&r=5116812 you can modify it by removing .EntireRow and adding a range or this one has lots of worked examples and explanations http://www.geocities.com/davemcritchie/excel/event.htm Regards Simon -- Simon Lloyd ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708 View this thread: http://www.excelforum.com/showthread...hreadid=561325 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA format of cells with colour based on IF formula | Excel Discussion (Misc queries) | |||
Conditional Format based on existing cell colour ? | Excel Discussion (Misc queries) | |||
How can I colour format all cells based on their values | Excel Worksheet Functions | |||
How do I set a colour to 4 cells based on the value of a cell | Excel Discussion (Misc queries) | |||
Conditional Format with VBA - Interior Colour of cell based on value from in-cell dropdown | Excel Programming |