Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - conditional formatting
I have a range from Q16:AJ28 and each cell contains one of five
statuses (LUC, LUD, CIP NL1, CIP NL2, CIP L, IRP). I am trying to write the VBA code to look through this range and determine if it's Open1, Selection.Interior.ColorIndex = 37, Open2 = Selection.Interior.ColorIndex = 33. I tried making an array of the cell's, but I don't know how to test the cell contents (an if statements?) An array also seemed a bit overkill. I also tried doing a For-to loop by offsetting the cells (Range(currentcell).Offset(0, 1).Address) but once again I couldn't test the cell contents - however, this does seem like a more logical approach. I also tried this code below, but it didn't work for anything with spaces (or something didn't work..) I appreciate the help! Dim icolor As Integer If Not Intersect(Target, Range("Q16:AJ28")) Is Nothing Then Select Case Target Case Is = "LUD" icolor = 37 Case "LUC" icolor = 33 Case "CIP NL1" icolor = 41 Case "CIP NL2" icolor = 5 Case "CIP L" icolor = 55 Case "IRP" icolor = 11 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - conditional formatting
Sub FormatColor()
Dim icolor As Long Dim Target as Range for each target in Range("Q16:AJ28") Select Case Ucase(Target) Case "LUD" icolor = 37 Case "LUC" icolor = 33 Case "CIP NL1" icolor = 41 Case "CIP NL2" icolor = 5 Case "CIP L" icolor = 55 Case "IRP" icolor = 11 Case Else icolor = xlNone End Select Target.Interior.ColorIndex = icolor End If Next Target End Sub -- Regards, Tom Ogilvy wrote in message oups.com... I have a range from Q16:AJ28 and each cell contains one of five statuses (LUC, LUD, CIP NL1, CIP NL2, CIP L, IRP). I am trying to write the VBA code to look through this range and determine if it's Open1, Selection.Interior.ColorIndex = 37, Open2 = Selection.Interior.ColorIndex = 33. I tried making an array of the cell's, but I don't know how to test the cell contents (an if statements?) An array also seemed a bit overkill. I also tried doing a For-to loop by offsetting the cells (Range(currentcell).Offset(0, 1).Address) but once again I couldn't test the cell contents - however, this does seem like a more logical approach. I also tried this code below, but it didn't work for anything with spaces (or something didn't work..) I appreciate the help! Dim icolor As Integer If Not Intersect(Target, Range("Q16:AJ28")) Is Nothing Then Select Case Target Case Is = "LUD" icolor = 37 Case "LUC" icolor = 33 Case "CIP NL1" icolor = 41 Case "CIP NL2" icolor = 5 Case "CIP L" icolor = 55 Case "IRP" icolor = 11 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting in Excel | New Users to Excel | |||
Excel Conditional Formatting | Excel Discussion (Misc queries) | |||
Excel Conditional Formatting | Excel Discussion (Misc queries) | |||
Excel and Conditional formatting | Excel Discussion (Misc queries) | |||
Conditional Formatting in Excel | Excel Worksheet Functions |