Excel VBA Conditional Format Column/Row Offset Based Upon Cell Condition

J

jhedges

Board Regular
Joined
May 27, 2009
Messages
208
I have an Excel spreadsheet, which is setup like a calendar. I can change the year at the top and the corresponding dates change in the days of the week columns. So, Monday thru Saturday are showing from E5:T5 and all the remaining rows are below. So, Monday is Merged in E5:G5, blank cells in E6,F6,G6, Date merged in E7:G7, two information merged cells in E8:G8 and E9:G9 and then individual cells in E10,F10,G10 thru E13,F13,G13. Tuesday is then in HIJ, Wednesday in KLM thru Friday in QRS and Saturday is then in T...Sunday is hidden in BCD. Then the pattern repeats for the entire year from row 6. The days of the week stay in row 5. What I would like is if the first six letters = "Closed" in the merged cells of row 9, 17, 25, 33, 41, 49, 57, 65, 73, 81, 89 up to 577 going up by 8, then the three columns and four rows below would be formatted in the light green color. So, if E9:G9 = "CLOSED MLK", then Cells E10,F10,G10 thru E13,F13,G13 would conditionally format to the light green color. This would repeat for each day of the week and all the way thru the end of the calendar in row 581. I can post an example if that helps? Just trying to keep from doing individual conditional formats as I change the year of the calendar...Any help would be appreciated...
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Russell Hauf said:
I suggest posting an example. See section B here: http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html
Click to expand...

I have posted an example below. What I would like to do via VBA is to look for the word "closed" in INFO row and color in the cells in the 3 columns and 4 rows below the cell with the word "closed" with light green. If the day is Saturday and "closed" is in the INFO row, then the cell in the 4th row below is colored light green.

The 9th row repeats again in rows 17, 25, 33, 41, 49, 57, 65, 73, 81, 89 up to 577 going up by 8

Master Schedule

*AEFGHIJKLMNOPQRST
F&HCIC****
WHBC*
F&HCIC*****
WHBC*

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 78px;"><col style="width: 66px;"><col style="width: 64px;"><col style="width: 77px;"><col style="width: 75px;"><col style="width: 64px;"><col style="width: 73px;"><col style="width: 76px;"><col style="width: 63px;"><col style="width: 64px;"><col style="width: 71px;"><col style="width: 66px;"><col style="width: 84px;"><col style="width: 76px;"><col style="width: 64px;"><col style="width: 78px;"><col style="width: 176px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="bgcolor: #FF0000, align: center"]2017[/TD]
[TD="bgcolor: #FF6600, colspan: 3, align: center"]MONDAY[/TD]
[TD="bgcolor: #FF6600, colspan: 3, align: center"]TUESDAY[/TD]
[TD="bgcolor: #FF6600, colspan: 3, align: center"]WEDNESDAY[/TD]
[TD="bgcolor: #FF6600, colspan: 3, align: center"]THURSDAY[/TD]
[TD="bgcolor: #FF6600, colspan: 3, align: center"]FRIDAY[/TD]
[TD="bgcolor: #FF6600, align: center"]SAT[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="bgcolor: #FF0000, align: center"]January[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD="bgcolor: #0066CC"]DATE:[/TD]
[TD="bgcolor: #0066CC, colspan: 3, align: center"]Jan-02[/TD]
[TD="bgcolor: #0066CC, colspan: 3, align: center"]Jan-03[/TD]
[TD="bgcolor: #0066CC, colspan: 3, align: center"]Jan-04[/TD]
[TD="bgcolor: #0066CC, colspan: 3, align: center"]Jan-05[/TD]
[TD="bgcolor: #0066CC, colspan: 3, align: center"]Jan-06[/TD]
[TD="bgcolor: #0066CC, align: center"]Jan-07[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD="bgcolor: #FFCC99"]VAC:[/TD]
[TD="bgcolor: #FFCC99, colspan: 3"]*[/TD]
[TD="bgcolor: #FFCC99, colspan: 3"]*[/TD]
[TD="bgcolor: #FFCC99, colspan: 3"]*[/TD]
[TD="bgcolor: #FFCC99, colspan: 3"]*[/TD]
[TD="bgcolor: #FFCC99, colspan: 3"]*[/TD]
[TD="bgcolor: #FFCC99"]*[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD="bgcolor: #CCCCFF"]INFO:[/TD]
[TD="bgcolor: #CCCCFF, colspan: 3, align: center"]CLOSED[/TD]
[TD="bgcolor: #CCCCFF, colspan: 3"]*[/TD]
[TD="bgcolor: #CCCCFF, colspan: 3"]*[/TD]
[TD="bgcolor: #CCCCFF, colspan: 3, align: center"]AH New Real. 8:30am[/TD]
[TD="bgcolor: #CCCCFF, colspan: 3"]*[/TD]
[TD="bgcolor: #CCCCFF"]*[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]
[TD="bgcolor: #FFFFCC"]GO[/TD]
[TD="bgcolor: #92D050"]*[/TD]
[TD="bgcolor: #92D050"]*[/TD]
[TD="bgcolor: #92D050"]*[/TD]
[TD="bgcolor: #FFFFCC, align: center"]NT[/TD]
[TD="bgcolor: #FFFFCC, align: center"]RP[/TD]
[TD="bgcolor: #FFFFCC, align: center"]MK[/TD]
[TD="bgcolor: #FFFFCC, align: center"]KK[/TD]
[TD="bgcolor: #FFFFCC, align: center"]NT/AH[/TD]
[TD="bgcolor: #FFFFCC, align: center"]MK[/TD]
[TD="bgcolor: #FFFFCC, align: center"]MK[/TD]
[TD="bgcolor: #FFFFCC, align: center"]PS[/TD]
[TD="bgcolor: #FFFFCC, align: center"]NT[/TD]
[TD="bgcolor: #FFFFCC, align: center"]RP[/TD]
[TD="bgcolor: #FFFFCC, align: center"]NT[/TD]
[TD="bgcolor: #FFFFCC, align: center"]MK[/TD]
[TD="bgcolor: #969696"]*[/TD]

[TD="bgcolor: #CACACA, align: center"]11[/TD]

[TD="bgcolor: #92D050"]*[/TD]
[TD="bgcolor: #92D050"]*[/TD]
[TD="bgcolor: #92D050"]*[/TD]
[TD="align: center"]AH[/TD]

[TD="align: center"]PS[/TD]
[TD="align: center"]RP[/TD]

[TD="align: center"]PS[/TD]
[TD="align: center"]RP[/TD]

[TD="align: center"]AH[/TD]
[TD="align: center"]PS[/TD]

[TD="align: center"]AH[/TD]
[TD="bgcolor: #969696"]*[/TD]

[TD="bgcolor: #CACACA, align: center"]12[/TD]
[TD="bgcolor: #FFFFCC"]MBC[/TD]
[TD="bgcolor: #92D050"]*[/TD]
[TD="bgcolor: #92D050"]*[/TD]
[TD="bgcolor: #92D050"]*[/TD]
[TD="bgcolor: #FFFFCC, align: center"]CD[/TD]
[TD="bgcolor: #FFFFCC, align: center"]JH[/TD]
[TD="bgcolor: #FFFFCC, align: center"]SAS[/TD]
[TD="bgcolor: #FFFFCC, align: center"]CD[/TD]
[TD="bgcolor: #FFFFCC"]*[/TD]
[TD="bgcolor: #FFFFCC, align: center"]SAS[/TD]
[TD="bgcolor: #FFFFCC, align: center"]SAS[/TD]
[TD="bgcolor: #FFFFCC, align: center"]CD[/TD]
[TD="bgcolor: #FFFFCC, align: center"]EG3-8[/TD]
[TD="bgcolor: #FFFFCC, align: center"]JH[/TD]
[TD="bgcolor: #FFFFCC, align: center"]SAS[/TD]
[TD="bgcolor: #FFFFCC, align: center"]CD[/TD]
[TD="bgcolor: #969696"]*[/TD]

[TD="bgcolor: #CACACA, align: center"]13[/TD]

[TD="bgcolor: #92D050"]*[/TD]
[TD="bgcolor: #92D050"]*[/TD]
[TD="bgcolor: #92D050"]*[/TD]
[TD="align: center"]BM[/TD]

[TD="align: center"]JK[/TD]
[TD="align: center"]EG5-10[/TD]
[TD="align: center"]JK/JH[/TD]
[TD="align: center"]BM[/TD]
[TD="align: center"]JK[/TD]
[TD="align: center"]JH[/TD]
[TD="align: center"]BM[/TD]
[TD="align: center"]JK[/TD]
[TD="align: center"]BM[/TD]
[TD="align: center"]EG2-7[/TD]
[TD="align: center"]NT[/TD]

[TD="bgcolor: #CACACA, align: center"]14[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]New Pay Period/Kronos[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]Payday[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]

[TD="bgcolor: #CACACA, align: center"]15[/TD]
[TD="bgcolor: #0066CC"]DATE:[/TD]
[TD="bgcolor: #0066CC, colspan: 3, align: center"]Jan-09[/TD]
[TD="bgcolor: #0066CC, colspan: 3, align: center"]Jan-10[/TD]
[TD="bgcolor: #0066CC, colspan: 3, align: center"]Jan-11[/TD]
[TD="bgcolor: #0066CC, colspan: 3, align: center"]Jan-12[/TD]
[TD="bgcolor: #0066CC, colspan: 3, align: center"]Jan-13[/TD]
[TD="bgcolor: #0066CC, align: center"]Jan-14[/TD]

[TD="bgcolor: #CACACA, align: center"]16[/TD]
[TD="bgcolor: #FFCC99"]VAC:[/TD]
[TD="bgcolor: #FFCC99, colspan: 3"]*[/TD]
[TD="bgcolor: #FFCC99, colspan: 3"]*[/TD]
[TD="bgcolor: #FFCC99, colspan: 3"]*[/TD]
[TD="bgcolor: #FFCC99, colspan: 3"]*[/TD]
[TD="bgcolor: #FFCC99, colspan: 3"]*[/TD]
[TD="bgcolor: #FFCC99"]*[/TD]

[TD="bgcolor: #CACACA, align: center"]17[/TD]
[TD="bgcolor: #CCCCFF"]INFO:[/TD]
[TD="bgcolor: #CCCCFF, colspan: 3"]*[/TD]
[TD="bgcolor: #CCCCFF, colspan: 3"]*[/TD]
[TD="bgcolor: #CCCCFF, colspan: 3"]*[/TD]
[TD="bgcolor: #CCCCFF, colspan: 3"]*[/TD]
[TD="bgcolor: #CCCCFF, colspan: 3"]*[/TD]
[TD="bgcolor: #CCCCFF, align: center"]CLOSED[/TD]

[TD="bgcolor: #CACACA, align: center"]18[/TD]
[TD="bgcolor: #FFFFCC"]GO[/TD]
[TD="bgcolor: #FFFFCC, align: center"]KK[/TD]
[TD="bgcolor: #FFFFCC, align: center"]RP/MK[/TD]
[TD="bgcolor: #FFFFCC, align: center"]NT[/TD]
[TD="bgcolor: #FFFFCC, align: center"]NT[/TD]
[TD="bgcolor: #FFFFCC, align: center"]RP[/TD]
[TD="bgcolor: #FFFFCC, align: center"]MK[/TD]
[TD="bgcolor: #FFFFCC, align: center"]KK[/TD]
[TD="bgcolor: #FFFFCC, align: center"]NT/AH[/TD]
[TD="bgcolor: #FFFFCC, align: center"]MK[/TD]
[TD="bgcolor: #FFFFCC, align: center"]MK[/TD]
[TD="bgcolor: #FFFFCC, align: center"]PS[/TD]
[TD="bgcolor: #FFFFCC, align: center"]NT[/TD]
[TD="bgcolor: #FFFFCC, align: center"]MK[/TD]
[TD="bgcolor: #FFFFCC, align: center"]RP[/TD]
[TD="bgcolor: #FFFFCC, align: center"]NT[/TD]
[TD="bgcolor: #969696"]*[/TD]

[TD="bgcolor: #CACACA, align: center"]19[/TD]

[TD="align: center"]PS[/TD]

[TD="align: center"]AH[/TD]
[TD="align: center"]AH[/TD]

[TD="align: center"]PS[/TD]
[TD="align: center"]RP[/TD]

[TD="align: center"]PS[/TD]
[TD="align: center"]RP[/TD]

[TD="align: center"]AH[/TD]
[TD="align: center"]AH[/TD]

[TD="align: center"]PS[/TD]
[TD="bgcolor: #969696"]*[/TD]

[TD="bgcolor: #CACACA, align: center"]20[/TD]
[TD="bgcolor: #FFFFCC"]MBC[/TD]
[TD="bgcolor: #FFFFCC, align: center"]SAS[/TD]
[TD="bgcolor: #FFFFCC, align: center"]JH[/TD]
[TD="bgcolor: #FFFFCC, align: center"]CD[/TD]
[TD="bgcolor: #FFFFCC, align: center"]CD[/TD]
[TD="bgcolor: #FFFFCC, align: center"]JH[/TD]
[TD="bgcolor: #FFFFCC, align: center"]SAS[/TD]
[TD="bgcolor: #FFFFCC, align: center"]CD[/TD]
[TD="bgcolor: #FFFFCC"]*[/TD]
[TD="bgcolor: #FFFFCC, align: center"]SAS[/TD]
[TD="bgcolor: #FFFFCC, align: center"]SAS[/TD]
[TD="bgcolor: #FFFFCC, align: center"]CD[/TD]
[TD="bgcolor: #FFFFCC, align: center"]EG3-8[/TD]
[TD="bgcolor: #FFFFCC, align: center"]CD[/TD]
[TD="bgcolor: #FFFFCC, align: center"]JH[/TD]
[TD="bgcolor: #FFFFCC, align: center"]SAS[/TD]
[TD="bgcolor: #969696"]*[/TD]

[TD="bgcolor: #CACACA, align: center"]21[/TD]

[TD="align: center"]BM[/TD]
[TD="align: center"]JK[/TD]
[TD="align: center"]EG3-8[/TD]
[TD="align: center"]BM[/TD]

[TD="align: center"]JK[/TD]
[TD="align: center"]EG5-10[/TD]
[TD="align: center"]JK/JH[/TD]
[TD="align: center"]BM[/TD]
[TD="align: center"]JK[/TD]
[TD="align: center"]JH[/TD]
[TD="align: center"]BM[/TD]
[TD="align: center"]BM[/TD]
[TD="align: center"]JK[/TD]
[TD="align: center"]EG2-7[/TD]
[TD="bgcolor: #92D050"]*[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
A5=CalendarYear
E6=IF(N6="Payday","New Pay Period/Kronos","")
N6=IF(ISERROR(INDEX(paydates,MATCH(N7,paydates,0))),"","Payday")
E7=IF(AND(YEAR(JanOffset+2)=CalendarYear,MONTH(JanOffset+2)=1),JanOffset+2,"")
H7=IF(AND(YEAR(JanOffset+3)=CalendarYear,MONTH(JanOffset+3)=1),JanOffset+3,"")
K7=IF(AND(YEAR(JanOffset+4)=CalendarYear,MONTH(JanOffset+4)=1),JanOffset+4,"")
N7=IF(AND(YEAR(JanOffset+5)=CalendarYear,MONTH(JanOffset+5)=1),JanOffset+5,"")
Q7=IF(AND(YEAR(JanOffset+6)=CalendarYear,MONTH(JanOffset+6)=1),JanOffset+6,"")
T7=IF(AND(YEAR(JanOffset+7)=CalendarYear,MONTH(JanOffset+7)=1),JanOffset+7,"")
E14=IF(N14="Payday","New Pay Period/Kronos","")
N14=IF(ISERROR(INDEX(paydates,MATCH(N15,paydates,0))),"","Payday")
E15=IF(AND(YEAR(JanOffset+9)=CalendarYear,MONTH(JanOffset+9)=1),JanOffset+9,"")
H15=IF(AND(YEAR(JanOffset+10)=CalendarYear,MONTH(JanOffset+10)=1),JanOffset+10,"")
K15=IF(AND(YEAR(JanOffset+11)=CalendarYear,MONTH(JanOffset+11)=1),JanOffset+11,"")
N15=IF(AND(YEAR(JanOffset+12)=CalendarYear,MONTH(JanOffset+12)=1),JanOffset+12,"")
Q15=IF(AND(YEAR(JanOffset+13)=CalendarYear,MONTH(JanOffset+13)=1),JanOffset+13,"")
T15=IF(AND(YEAR(JanOffset+14)=CalendarYear,MONTH(JanOffset+14)=1),JanOffset+14,"")

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4
 
Upvote 0
I'd do something like this. You'll need to account for when you might erroneously change a cell to CLOSED...I'd maybe do a format copy of a pre-defined range of cells (could be on a different - maybe hidden? - sheet, or could be within your main data sheet). I've left that part for you for now - if you can't figure it out let us know and someone will code something up for you.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If (Target.Row - 1) Mod 8 = 0 And Target.Text = "CLOSED" Then
            Select Case UCase(Format(Target.Offset(-2), "ddd"))
                Case "SAT"
                    Target.Offset(4).Interior.Color = 5296274
                Case Else
                    Target.Offset(1, 0).Resize(4, 3).Interior.Color = 5296274
            End Select
    Else
        ' IF TARGET.VALUE <> CLOSED, DO YOU WANT TO CHANGE BACK,
        ' IN CASE YOU PUT "CLOSED" ON THE WRONG DATE?
    End If
End Sub

Note that Closed is not the same as CLOSED. If you don't care about case, then use UCASE(Target.Text).
 
Upvote 0
Russell - thanks for this response. I apologize for not responding at all to this earlier...It is something we do once per year and the beginning of the 3rd qtr. begins the busy season for us and I'm just now getting back to this.

This is working fine; however, I have a questions on if I want to change back if I put "CLOSED" on the wrong date. I can copy the code above to get it to change back; however, how do I color my rows back to the two different alternating colors? I'm using light yellow and white as the alternating colors.
 
Upvote 0
This is the code I have so far:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If (Target.Row - 1) Mod 8 = 0 And Target.text = "CLOSED" Then
            Select Case UCase(Format(Target.offset(-2), "ddd"))
                Case "SAT"
                    Target.offset(4).Interior.Color = 5296274
                Case Else
                    Target.offset(1, 0).Resize(4, 3).Interior.Color = 5296274
            End Select
    Else
    If Target.Value <> "CLOSED" Then
            Select Case UCase(Format(Target.offset(-2), "ddd"))
                Case "SAT"
                    Target.offset(4).Interior.Color = 2
                Case Else
                    Target.offset(1, 0).Resize(4, 3).Interior.Color = 13759225
            End Select
     End If
    End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Indicating a zero total in a range of cells by colouring the cells either by formula or Conditional Formatting
Replies
1
Views
139
Peter_SSs
Peter_SSs
A
  • Solved
Conditional Formatting - apply rules to multiple rows based on specific cells (drop down menus)
Replies
4
Views
420
AndyB86
A
G
  • Question Question
Conditional Formatting based on another cell column data
Replies
2
Views
564
gulonraid
G
P
  • Question Question
Applying conditional formatting to merged cells
Replies
1
Views
638
Joe4
Joe4
G
  • Question Question
Conditional Formatting for Various Dates
Replies
4
Views
752
MountainFog
M

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top