Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VBA for increased conditional formatting choices
Hi, apologies if this has been answered on a previous thread, but I've read through many of them and as I'm new to VBA I don't seem to be getting anywhere!
My problem is the age-old one of needing more than three conditional format values. In column A I have a series of statements. In column B, users of this sheet are asked to answer these statements using a data validated pick list of 5 choices (the source for which is a list in cells C9:C13). I need to create some VBA that will colour the font of choice 1 in red, choice 2 in amber, choice 3 in yellow, choice 4 in green and choice 5 in grey. Any assistance would be gratefully received! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA for increased conditional formatting choices
Gra
Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Variant Set vRngInput = Intersect(Target, Range("C9:C13")) If vRngInput Is Nothing Then Exit Sub On Error GoTo enditall Application.EnableEvents = False For Each rng In vRngInput 'Determine the color Select Case rng.Value Case Is = 1: Num = 3 'red Case Is = 2: Num = 46 'orange Case Is = 3: Num = 6 'yellow Case Is = 4: Num = 10 'green Case Is = 5: Num = 15 '25% gray End Select 'Apply the color rng.Interior.ColorIndex = Num Next rng enditall: Application.EnableEvents = True End Sub This is sheet event code. Right-click on the sheet tab and "View Code" Copy/paste the above into that module. Don't know what index number for Amber so gave you Orange. Gord Dibben MS Excel MVP On Thu, 13 Jul 2006 16:11:55 +0100, Gra wrote: Hi, apologies if this has been answered on a previous thread, but I've read through many of them and as I'm new to VBA I don't seem to be getting anywhere! My problem is the age-old one of needing more than three conditional format values. In column A I have a series of statements. In column B, users of this sheet are asked to answer these statements using a data validated pick list of 5 choices (the source for which is a list in cells C9:C13). I need to create some VBA that will colour the font of choice 1 in red, choice 2 in amber, choice 3 in yellow, choice 4 in green and choice 5 in grey. Any assistance would be gratefully received! |
#3
|
|||
|
|||
Many thanks!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting glitches | Excel Discussion (Misc queries) | |||
Keeping conditional formatting when sorting | Excel Discussion (Misc queries) | |||
conditional formatting | Excel Discussion (Misc queries) | |||
Conditional Formatting | Excel Worksheet Functions | |||
cannot use ISEVEN or ISODD functions in Conditional Formatting | Excel Worksheet Functions |