| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting color
Using Excel 2003, I have downloaded John Walkenbach's calender
http://spreadsheetpage.com/index.php...with_holidays/ and added at the bottom columns for date A55:A88 and N55:N88 and columns for code with a drop down list with 8 different entries B55:B88 and N55:N88. I would like to conditional format the corresponding date on the calendar with the matching color for the code column. Based on his formula for the holidays =NOT(ISNA(MATCH(G7,holidays,0))) (which was formatted in each cell), how would you put in a range for 7 different codes, as holiday is already formatted? I have created a name for the date ranges as summary. I assume VB is needed and I had the following based on community input that I found: Summary Formula Is = NOT(ISNA(MATCH(A17:Y49,summary,0))) ' On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(A17:Y49)) Is Nothing Then With Target Select Case .Value Case "A": .Interior.ColorIndex = 3 'red Case "AD": .Interior.ColorIndex = 5 'blue Case "D": .Interior.ColorIndex = 39 'purple Case "L": .Interior.ColorIndex = 46 'orange Case "O": .Interior.ColorIndex = 15 'grey Case "P": .Interior.ColorIndex = 27 'yellow Case "T": .Interior.ColorIndex = 43 'light green Case "V": .Interior.ColorIndex = 22 'pink End Select End With End If ws_exit: Application.EnableEvents = True End Sub A17:Y49 it the range of the calendar. I am new to VB. Any help would be appreciated. Thanks in advance. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional formatting color
Here is the code........
(Insert in a standard module... it will run on the current sheet) Sub markDates() Dim i, j, k, dt 'Copy this loop till the line marked with END 'and change A to N (or M?) in 'dt = Range("A" & k).Value 'and B to O (or N if changed to M above) in 'dt = Range("B" & k).Value For k = 55 To 88 dt = Range("A" & k).Value For i = 7 To 39 For j = 3 To 25 If Cells(i, j).Value = dt Then Flag = 1 With Cells(i, j) code = Range("B" & k).Value Select Case code Case "A": .Interior.ColorIndex = 3 'red Case "AD": .Interior.ColorIndex = 5 'blue Case "D": .Interior.ColorIndex = 39 'purple Case "L": .Interior.ColorIndex = 46 'orange Case "O": .Interior.ColorIndex = 15 'grey Case "P": .Interior.ColorIndex = 27 'yellow Case "T": .Interior.ColorIndex = 43 'light green Case "V": .Interior.ColorIndex = 22 'pink End Select End With Exit For End If Next j If Flag = 1 Then Flag = 0 Exit For End If Next i Next k 'END End Sub -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Sandy" wrote: Using Excel 2003, I have downloaded John Walkenbach's calender http://spreadsheetpage.com/index.php...with_holidays/ and added at the bottom columns for date A55:A88 and N55:N88 and columns for code with a drop down list with 8 different entries B55:B88 and N55:N88. I would like to conditional format the corresponding date on the calendar with the matching color for the code column. Based on his formula for the holidays =NOT(ISNA(MATCH(G7,holidays,0))) (which was formatted in each cell), how would you put in a range for 7 different codes, as holiday is already formatted? I have created a name for the date ranges as summary. I assume VB is needed and I had the following based on community input that I found: Summary Formula Is = NOT(ISNA(MATCH(A17:Y49,summary,0))) ' On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(A17:Y49)) Is Nothing Then With Target Select Case .Value Case "A": .Interior.ColorIndex = 3 'red Case "AD": .Interior.ColorIndex = 5 'blue Case "D": .Interior.ColorIndex = 39 'purple Case "L": .Interior.ColorIndex = 46 'orange Case "O": .Interior.ColorIndex = 15 'grey Case "P": .Interior.ColorIndex = 27 'yellow Case "T": .Interior.ColorIndex = 43 'light green Case "V": .Interior.ColorIndex = 22 'pink End Select End With End If ws_exit: Application.EnableEvents = True End Sub A17:Y49 it the range of the calendar. I am new to VB. Any help would be appreciated. Thanks in advance. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional formatting color
I have added another loop so you don't have to copy....
'If your values are in M and N then change N to M and O to N in the code below ------------------------------------- Sub markDates() Dim i, j, k, l, dt Dim rng(2, 2) As String rng(1, 1) = "A" rng(1, 2) = "B" 'If your values are in M and N then change N to M and O to N below rng(2, 1) = "N" rng(2, 2) = "O" For l = 1 To 2 For k = 55 To 88 dt = Range(rng(l, 1) & k).Value For i = 7 To 39 For j = 3 To 25 If Cells(i, j).Value = dt Then Flag = 1 With Cells(i, j) code = Range(rng(l, 2) & k).Value Select Case code Case "A": .Interior.ColorIndex = 3 'red Case "AD": .Interior.ColorIndex = 5 'blue Case "D": .Interior.ColorIndex = 39 'purple Case "L": .Interior.ColorIndex = 46 'orange Case "O": .Interior.ColorIndex = 15 'grey Case "P": .Interior.ColorIndex = 27 'yellow Case "T": .Interior.ColorIndex = 43 'light green Case "V": .Interior.ColorIndex = 22 'pink End Select End With Exit For End If Next j If Flag = 1 Then Flag = 0 Exit For End If Next i Next k Next l End Sub -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Sheeloo" wrote: Here is the code........ (Insert in a standard module... it will run on the current sheet) Sub markDates() Dim i, j, k, dt 'Copy this loop till the line marked with END 'and change A to N (or M?) in 'dt = Range("A" & k).Value 'and B to O (or N if changed to M above) in 'dt = Range("B" & k).Value For k = 55 To 88 dt = Range("A" & k).Value For i = 7 To 39 For j = 3 To 25 If Cells(i, j).Value = dt Then Flag = 1 With Cells(i, j) code = Range("B" & k).Value Select Case code Case "A": .Interior.ColorIndex = 3 'red Case "AD": .Interior.ColorIndex = 5 'blue Case "D": .Interior.ColorIndex = 39 'purple Case "L": .Interior.ColorIndex = 46 'orange Case "O": .Interior.ColorIndex = 15 'grey Case "P": .Interior.ColorIndex = 27 'yellow Case "T": .Interior.ColorIndex = 43 'light green Case "V": .Interior.ColorIndex = 22 'pink End Select End With Exit For End If Next j If Flag = 1 Then Flag = 0 Exit For End If Next i Next k 'END End Sub -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Sandy" wrote: Using Excel 2003, I have downloaded John Walkenbach's calender http://spreadsheetpage.com/index.php...with_holidays/ and added at the bottom columns for date A55:A88 and N55:N88 and columns for code with a drop down list with 8 different entries B55:B88 and N55:N88. I would like to conditional format the corresponding date on the calendar with the matching color for the code column. Based on his formula for the holidays =NOT(ISNA(MATCH(G7,holidays,0))) (which was formatted in each cell), how would you put in a range for 7 different codes, as holiday is already formatted? I have created a name for the date ranges as summary. I assume VB is needed and I had the following based on community input that I found: Summary Formula Is = NOT(ISNA(MATCH(A17:Y49,summary,0))) ' On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(A17:Y49)) Is Nothing Then With Target Select Case .Value Case "A": .Interior.ColorIndex = 3 'red Case "AD": .Interior.ColorIndex = 5 'blue Case "D": .Interior.ColorIndex = 39 'purple Case "L": .Interior.ColorIndex = 46 'orange Case "O": .Interior.ColorIndex = 15 'grey Case "P": .Interior.ColorIndex = 27 'yellow Case "T": .Interior.ColorIndex = 43 'light green Case "V": .Interior.ColorIndex = 22 'pink End Select End With End If ws_exit: Application.EnableEvents = True End Sub A17:Y49 it the range of the calendar. I am new to VB. Any help would be appreciated. Thanks in advance. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional formatting color
The formula worked until I deleted my date. The color stayed. When I
scrolled throught more years, the color formatting stayed on that specific day even thought there weren't any dates. Any suggestions? "Sheeloo" wrote: I have added another loop so you don't have to copy.... 'If your values are in M and N then change N to M and O to N in the code below ------------------------------------- Sub markDates() Dim i, j, k, l, dt Dim rng(2, 2) As String rng(1, 1) = "A" rng(1, 2) = "B" 'If your values are in M and N then change N to M and O to N below rng(2, 1) = "N" rng(2, 2) = "O" For l = 1 To 2 For k = 55 To 88 dt = Range(rng(l, 1) & k).Value For i = 7 To 39 For j = 3 To 25 If Cells(i, j).Value = dt Then Flag = 1 With Cells(i, j) code = Range(rng(l, 2) & k).Value Select Case code Case "A": .Interior.ColorIndex = 3 'red Case "AD": .Interior.ColorIndex = 5 'blue Case "D": .Interior.ColorIndex = 39 'purple Case "L": .Interior.ColorIndex = 46 'orange Case "O": .Interior.ColorIndex = 15 'grey Case "P": .Interior.ColorIndex = 27 'yellow Case "T": .Interior.ColorIndex = 43 'light green Case "V": .Interior.ColorIndex = 22 'pink End Select End With Exit For End If Next j If Flag = 1 Then Flag = 0 Exit For End If Next i Next k Next l End Sub -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Sheeloo" wrote: Here is the code........ (Insert in a standard module... it will run on the current sheet) Sub markDates() Dim i, j, k, dt 'Copy this loop till the line marked with END 'and change A to N (or M?) in 'dt = Range("A" & k).Value 'and B to O (or N if changed to M above) in 'dt = Range("B" & k).Value For k = 55 To 88 dt = Range("A" & k).Value For i = 7 To 39 For j = 3 To 25 If Cells(i, j).Value = dt Then Flag = 1 With Cells(i, j) code = Range("B" & k).Value Select Case code Case "A": .Interior.ColorIndex = 3 'red Case "AD": .Interior.ColorIndex = 5 'blue Case "D": .Interior.ColorIndex = 39 'purple Case "L": .Interior.ColorIndex = 46 'orange Case "O": .Interior.ColorIndex = 15 'grey Case "P": .Interior.ColorIndex = 27 'yellow Case "T": .Interior.ColorIndex = 43 'light green Case "V": .Interior.ColorIndex = 22 'pink End Select End With Exit For End If Next j If Flag = 1 Then Flag = 0 Exit For End If Next i Next k 'END End Sub -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Sandy" wrote: Using Excel 2003, I have downloaded John Walkenbach's calender http://spreadsheetpage.com/index.php...with_holidays/ and added at the bottom columns for date A55:A88 and N55:N88 and columns for code with a drop down list with 8 different entries B55:B88 and N55:N88. I would like to conditional format the corresponding date on the calendar with the matching color for the code column. Based on his formula for the holidays =NOT(ISNA(MATCH(G7,holidays,0))) (which was formatted in each cell), how would you put in a range for 7 different codes, as holiday is already formatted? I have created a name for the date ranges as summary. I assume VB is needed and I had the following based on community input that I found: Summary Formula Is = NOT(ISNA(MATCH(A17:Y49,summary,0))) ' On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(A17:Y49)) Is Nothing Then With Target Select Case .Value Case "A": .Interior.ColorIndex = 3 'red Case "AD": .Interior.ColorIndex = 5 'blue Case "D": .Interior.ColorIndex = 39 'purple Case "L": .Interior.ColorIndex = 46 'orange Case "O": .Interior.ColorIndex = 15 'grey Case "P": .Interior.ColorIndex = 27 'yellow Case "T": .Interior.ColorIndex = 43 'light green Case "V": .Interior.ColorIndex = 22 'pink End Select End With End If ws_exit: Application.EnableEvents = True End Sub A17:Y49 it the range of the calendar. I am new to VB. Any help would be appreciated. Thanks in advance. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional formatting color
Use this to clear the colors
Sub ClearCells() For i = 7 To 39 For j = 3 To 25 Cells(i, j).Interior.ColorIndex = 0 Next j Next i End Sub "Sandy" wrote: The formula worked until I deleted my date. The color stayed. When I scrolled throught more years, the color formatting stayed on that specific day even thought there weren't any dates. Any suggestions? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional formatting color
My boxes in the upper left corner now clears color when macro runs. I
e-mailed a copy of my file. Hope this helps explain a little better. Thanks. "Sheeloo" wrote: Use this to clear the colors Sub ClearCells() For i = 7 To 39 For j = 3 To 25 Cells(i, j).Interior.ColorIndex = 0 Next j Next i End Sub "Sandy" wrote: The formula worked until I deleted my date. The color stayed. When I scrolled throught more years, the color formatting stayed on that specific day even thought there weren't any dates. Any suggestions? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Color Banding / Conditional Formatting | Excel Discussion (Misc queries) | |||
| Conditional formatting to color cells | Excel Discussion (Misc queries) | |||
| Conditional Formatting on Color | Excel Worksheet Functions | |||
| Color Conditional Formatting | Excel Worksheet Functions | |||
| Conditional color formatting entries have wild color. | Excel Discussion (Misc queries) | |||
Hybrid Mode
