Solved: LOOPING through a date range?

[emm]

For just one row:

Setup:

Range A2 houses a name
Range B2:C2 houses a start and end date formatted dd mmm yy
Range D1:AH1 houses days of a month custom formatted to "d"

Anytime data is changed in Range A2:C2, I want to put the day of the month from Range D1:AH1 in range D2:AH2 when they match or are between dates in B2:C2 This would be a Gantt Chart but with dates unstead of shading...

I am familar with Private Sub Worksheet_Change(ByVal Target As Range) but can not figure out how to do this loop.

[acw]

Hi

This will put in the full date and format to the day in the same way that row 1 is formatted.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column < 4 Then
Range("D" & Target.Row & ":AH" & Target.Row).ClearContents
Range(Cells(Target.Row, 3 + Day(Cells(Target.Row, 2))), Cells(Target.Row, 3 + Day(Cells(Target.Row, 3)))).Value = _
Range(Cells(1, 3 + Day(Cells(Target.Row, 2))), Cells(1, 3 + Day(Cells(Target.Row, 3)))).Value
Range(Cells(Target.Row, 3 + Day(Cells(Target.Row, 2))), Cells(Target.Row, 3 + Day(Cells(Target.Row, 3)))).NumberFormat = _
Range(Cells(1, 3 + Day(Cells(Target.Row, 2))), Cells(1, 3 + Day(Cells(Target.Row, 3)))).NumberFormat
End If
End Sub


Tony

[acw]

Hi

Another options with only numbers

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column < 4 Then
Range("D" & Target.Row & ":AH" & Target.Row).ClearContents
For i = Day(Cells(Target.Row, 2)) + 3 To Day(Cells(Target.Row, 3)) + 3
Cells(Target.Row, i).Value = i - 3
Next i
End If
End Sub



Tony

[emm]

ACW - thanks a bunch, couple to questions:

1. When I click any cell in the target range and don't put a date the last day of the month shows up under the last day of the month.
-- That might be OK, will have to work with it a few days.

2. When I put a date in columns B and C the date goes from "start" (column B) date to last day of the month until I put a name in column A then the date works itself out?
-- That might be OK, will have to work with it a few days.

3. What I'm trying to do is make a Gantt Chart where I can show tasks that each office is suppose to do by date. I will add another column for that later.

Here is some code that Erik Van Geit helped me and yours added:

- It colors range A:C based on the office name in column A.
- I want to use that same office color where your code puts the date.
- I use condition formatting to color the weekends, this might cause a problem?

Option Explicit
Option Base 1
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Erik Van Geit
'051116
Dim WatchRange As Range
Dim cell As Range
Dim colors As Variant
Dim i As Integer
Dim inputs As Variant
Dim col As Integer
Set WatchRange = Range("A2:AM" & Rows.Count)
If Intersect(Target, WatchRange) Is Nothing Then Exit Sub
'ACW starts here
If Target.Column < 4 Then
Range("D" & Target.Row & ":AH" & Target.Row).ClearContents
For i = Day(Cells(Target.Row, 2)) + 3 To Day(Cells(Target.Row, 3)) + 3
Cells(Target.Row, i).Value = i - 3
Next i
End If
'ACW ends here
Range("D" & Target.Row & ":AH" & Target.Row).Interior.ColorIndex = 0
inputs = Array("Office A", "Office B", "Office C", "Office D", "Office E", "Office F")
colors = Array(3, 4, 5, 6, 7, 8)

For Each cell In Target
i = 0
On Error Resume Next
i = Application.Match(cell.Value, inputs, 0)
On Error GoTo 0
If i <> 0 Then col = colors(i) Else: col = xlNone
cell.Interior.ColorIndex = col
Intersect(cell.EntireRow, Columns("A:C")).Interior.ColorIndex = col
Next cell

End Sub

[emm]

acw - which "line of code" line is looking at the row that has the month dates.

In the real file the dates are in row 11.

I plugged the code in the real file and it is not putting the dates in, but the sample file works fine with the month dates in row 1??

GOT THE REAL FILE WORKING...

[emm]

I "think" I understand--you code does not need the date row because your code is using the changed row for calculating the dates and +3 and -3 take care that the A:C will not house dates??

[Bob Phillips]

Try this


Option Explicit

Option Base 1
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim cell As Range
Dim colors As Variant
Dim i As Integer
Dim inputs As Variant
Dim col As Long
Dim iStart As Long
Dim iEnd As Long

On Error GoTo ws_exit
Application.EnableEvents = False

inputs = Array("Office A", "Office B", "Office C", "Office D", "Office E", "Office F")
colors = Array(3, 4, 5, 6, 7, 8)

Set WatchRange = Range("A2:AM" & Rows.Count)
If Intersect(Target, WatchRange) Is Nothing Then Exit Sub
'ACW starts here
With Target
Range("D" & .Row & ":AH" & .Row).ClearContents
If .Column < 4 Then
If Cells(.Row, "B").Value <> "" Then
iStart = Day(Cells(.Row, "B"))
End If
If Cells(.Row, "C").Value <> "" Then
iEnd = Day(Cells(.Row, 3))
End If
If iStart <> 0 And iEnd <> 0 Then
For i = iStart + 3 To iEnd + 3
Cells(.Row, i).Value = i - 3
Next i
End If
End If

Range("D" & .Row & ":AH" & .Row).Interior.ColorIndex = xlColorIndexNone
On Error Resume Next
i = Application.Match(Cells(.Row, "A").Value, inputs, 0)
On Error GoTo 0
If i <> 0 Then
Cells(.Row, "A").Resize(, Application.Count(Range("D" & .Row).Resize(, 31)) + 3).Interior.ColorIndex = colors(i)
End If

End With
ws_exit:
Application.EnableEvents = True
End Sub

[emm]

xld - we are close...

I input:

in A2: Office A

in B2: 3 Nov

in C2: 6 Nov

The office color "started" at Nov 1 and "stopped" at Nov 4,

but it should have "started" 3 Nov and "stopped" at 6 Nov...

[Bob Phillips]

xld - we are close...

I input:

in A2: Office A

in B2: 3 Nov

in C2: 6 Nov

The office color "started" at Nov 1 and "stopped" at Nov 4,

but it should have "started" 3 Nov and "stopped" at 6 Nov...

Of course, sloppy.


Option Explicit
Option Base 1

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim cell As Range
Dim colors As Variant
Dim i As Integer
Dim inputs As Variant
Dim col As Long
Dim iStart As Long
Dim iEnd As Long

On Error GoTo ws_exit
Application.EnableEvents = False

inputs = Array("Office A", "Office B", "Office C", "Office D", "Office E", "Office F")
colors = Array(3, 4, 5, 6, 7, 8)

Set WatchRange = Range("A2:AM" & Rows.Count)
If Intersect(Target, WatchRange) Is Nothing Then Exit Sub
'ACW starts here
With Target
Range("D" & .Row & ":AH" & .Row).ClearContents
If .Column < 4 Then
If Cells(.Row, "B").Value <> "" Then
iStart = Day(Cells(.Row, "B"))
End If
If Cells(.Row, "C").Value <> "" Then
iEnd = Day(Cells(.Row, 3))
End If
If iStart <> 0 And iEnd <> 0 Then
For i = iStart + 3 To iEnd + 3
Cells(.Row, i).Value = i - 3
Next i
End If
End If

Range("D" & .Row & ":AH" & .Row).Interior.ColorIndex = xlColorIndexNone
On Error Resume Next
i = Application.Match(Cells(.Row, "A").Value, inputs, 0)
On Error GoTo 0
If i <> 0 Then
Cells(.Row, "A").Resize(, 3).Interior.ColorIndex = colors(i)
Cells(.Row, iStart + 3).Resize(, Application.Count(Range("D" & .Row).Resize(, 31))).Interior.ColorIndex = colors(i)
End If

End With
ws_exit:
Application.EnableEvents = True
End Sub

[emm]

xld - that works great. I'm gonna hold off on hitting the Solved button for a day.

The only thing I might need is to connect a button so update the entire sheet ocassionly. I would think about updating everytime any change is made to the sheet, but that might slow things down--anyway I will run it today and see what happens...

[emm]

I was converting this to the real file and had a break here (this is last rows of code):

I changed all 3s to 12s but it only broke at the one in red below.

Resize(, 31) - is this just for the most number of days in a month?

If i <> 0 Then
Cells(.Row, "A").Resize(, 3).Interior.ColorIndex = colors(i)
Cells(.Row, iStart + 3).Resize(, Application.Count(Range("D" & .Row).Resize(, 31))).Interior.ColorIndex = colors(i)
End If

found it:

Immediate Window, paste Application.EnableEvents = True, hit enter

[emm]

found it:

Immediate Window, paste Application.EnableEvents = True, hit enter

I see what happens, if the code breaks before in gets to that line you have to reset it using the Immediate Window - correct?

[Bob Phillips]

found it:

Immediate Window, paste Application.EnableEvents = True, hit enter

I see what happens, if the code breaks before in gets to that line you have to reset it using the Immediate Window - correct?

No not really. The code has error handling to automatically reset it. You must have had a code break to get it unset, in those cases you are correct.

[emm]

xld - code breaking as indicated below:

1. When I input an office name and then hit enter the code breaks (no dates in B and C).
2. When I delete either of the dates, or one date at a time the code breaks.

If i <> 0 Then
Cells(.Row, "A").Resize(, 3).Interior.ColorIndex = colors(i)
Cells(.Row, iStart + 3).Resize(, Application.Count(Range("D" & .Row).Resize(, 31))).Interior.ColorIndex = colors(i)
End If

[Bob Phillips]

xld - code breaking as indicated below:

1. When I input an office name and then hit enter the code breaks (no dates in B and C).
2. When I delete either of the dates, or one date at a time the code breaks.

If i <> 0 Then
Cells(.Row, "A").Resize(, 3).Interior.ColorIndex = colors(i)
Cells(.Row, iStart + 3).Resize(, Application.Count(Range("D" & .Row).Resize(, 31))).Interior.ColorIndex = colors(i)
End If

Here is another correction.

A question for you. What would you expect if you put 1st Oct as start and 12th Nov as end? It looks odd to me.


Option Explicit
Option Base 1

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim cell As Range
Dim colors As Variant
Dim i As Integer
Dim inputs As Variant
Dim col As Long
Dim iStart As Long
Dim iEnd As Long

On Error GoTo ws_exit
Application.EnableEvents = False

inputs = Array("Office A", "Office B", "Office C", "Office D", "Office E", "Office F")
colors = Array(3, 4, 5, 6, 7, 8)

Set WatchRange = Range("A2:AM" & Rows.Count)
If Intersect(Target, WatchRange) Is Nothing Then Exit Sub
With Target
Range("D" & .Row & ":AH" & .Row).ClearContents
Range("A" & .Row & ":AH" & .Row).Interior.ColorIndex = xlColorIndexNone
If .Column < 4 Then
If Cells(.Row, "B").Value <> "" Then
iStart = Day(Cells(.Row, "B"))
End If
If Cells(.Row, "C").Value <> "" Then
iEnd = Day(Cells(.Row, 3))
End If
If iStart <> 0 And iEnd <> 0 Then
For i = iStart + 3 To iEnd + 3
Cells(.Row, i).Value = i - 3
Next i

On Error Resume Next
i = Application.Match(Cells(.Row, "A").Value, inputs, 0)
On Error GoTo 0
If i <> 0 Then
Cells(.Row, "A").Resize(, 3).Interior.ColorIndex = colors(i)
Cells(.Row, iStart + 3).Resize(, _
Application.Count(Range("D" & .Row).Resize(, 31))).Interior.ColorIndex = colors(i)
End If
End If
End If

End With
ws_exit:
Application.EnableEvents = True
End Sub

[emm]

Wellllll, I have 12 sheets--one for each month.

So on the Oct sheet it would show, for that particular row, colored from 1 Oct to 31 Oct and then I would have to go to the other sheet and I would pick the date up at 1 Nov to 15 Nov.

Is that a bad idea?

[Bob Phillips]

Wellllll, I have 12 sheets--one for each month.

So on the Oct sheet it would show, for that particular row, colored from 1 Oct to 31 Oct and then I would have to go to the other sheet and I would pick the date up at 1 Nov to 15 Nov.

Is that a bad idea?

No it wasn't that I was really raising.

As an example, enter a start date of 1st Oct and and en end date of 10th Nov, and 1-10 get coloured. It is a data entry error, but there is nothing to point this out to you (the user?).

Bob

[emm]

No it wasn't that I was really raising.

As an example, enter a start date of 1st Oct and and en end date of 10th Nov, and 1-10 get coloured. It is a data entry error, but there is nothing to point this out to you (the user?).

Bob

hmmm, interesting.

1. I'd rather be able to enter the entire date range on one sheet and then input the differnce manually on the next sheet.

- remember, I have dates running accross the top for each month that originally was used to match intersecting dates, but your your, and acw's code, seems much faster...

2. Question, what does the 31 in this line represent, most days in a month?

Application.Count(Range("D" & .Row).Resize(, 31))).Interior.ColorIndex = colors(i)

[Bob Phillips]

hmmm, interesting.

1. I'd rather be able to enter the entire date range on one sheet and then input the differnce manually on the next sheet.

- remember, I have dates running accross the top for each month that originally was used to match intersecting dates, but your your, and acw's code, seems much faster...

Not really sure what you mean here.


2. Question, what does the 31 in this line represent, most days in a month?

Application.Count(Range("D" & .Row).Resize(, 31))).Interior.ColorIndex = colors(i)

Yes, that is correct.

[emm]

Sample File Setup:

Range A2 houses a name
Range B2:C2 houses a start and end date formatted dd mmm yy
Range D1:AH1 houses days of a month custom formatted to "d"

Real File Setup:
Range A12 and down houses name (use data validation to make sure exact)
Range K12:L12 and down house start and end dates
Range M11:AQ11 house days of a month custom formatted to "d"

I have changed all to:

- Bs to Ks and Cs to Ls
- Ds to Ms and QHs to AQs
- changed all 3s to 12s

I say this becasue I keep getting a break here and can't figure out why:

Cells(.Row, "A").Resize(, 12).Interior.ColorIndex = colors(i)

[Bob Phillips]

Sample File Setup:

Range A2 houses a name
Range B2:C2 houses a start and end date formatted dd mmm yy
Range D1:AH1 houses days of a month custom formatted to "d"

Real File Setup:
Range A12 and down houses name (use data validation to make sure exact)
Range K12:L12 and down house start and end dates
Range M11:AQ11 house days of a month custom formatted to "d"

I have changed all to:

- Bs to Ks and Cs to Ls
- Ds to Ms and QHs to AQs
- changed all 3s to 12s

I say this becasue I keep getting a break here and can't figure out why:

Cells(.Row, "A").Resize(, 12).Interior.ColorIndex = colors(i)

Why give an example that is different to real?

This should be more configurable.


Option Explicit
Option Base 1

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const START_DATE_COL As Long = 11
Const END_DATE_COL As Long = 12
Const FIRST_DAY_COL As Long = 13
Dim WatchRange As Range
Dim cell As Range
Dim colors As Variant
Dim i As Integer
Dim inputs As Variant
Dim col As Long
Dim iStart As Long
Dim iEnd As Long

On Error GoTo ws_exit
Application.EnableEvents = False
Application.ScreenUpdating = False

inputs = Array("Office A", "Office B", "Office C", "Office D", "Office E", "Office F")
colors = Array(3, 4, 5, 6, 7, 8)

Set WatchRange = Range("A2", Cells(Rows.Count, "A").End(xlUp)).Resize(, FIRST_DAY_COL - 1)
If Intersect(Target, WatchRange) Is Nothing Then Exit Sub
With Target
Cells(.Row, FIRST_DAY_COL).Resize(, 31).Clear
If .Column < FIRST_DAY_COL Then
If Cells(.Row, START_DATE_COL).Value <> "" Then
iStart = Day(Cells(.Row, START_DATE_COL))
End If
If Cells(.Row, END_DATE_COL).Value <> "" Then
iEnd = Day(Cells(.Row, END_DATE_COL))
End If
If iStart <> 0 And iEnd <> 0 Then
For i = iStart To iEnd
Cells(.Row, i + FIRST_DAY_COL - 1).Value = i
Next i

i = 0
On Error Resume Next
i = Application.Match(Cells(.Row, "A").Value, inputs, 0)
On Error GoTo 0
If i = 0 Then
Cells(.Row, "A").Resize(, FIRST_DAY_COL - 1).Interior.ColorIndex = xlColorIndexNone
Else
Cells(.Row, "A").Resize(, FIRST_DAY_COL - 1).Interior.ColorIndex = colors(i)
Cells(.Row, iStart + FIRST_DAY_COL - 1).Resize(, _
Application.Count(Cells(.Row, FIRST_DAY_COL).Resize(, 31))).Interior.ColorIndex = colors(i)
End If
End If
End If

End With
ws_exit:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

[emm]

xld - I thought it would be easier for you to build the code-- I apologize because it actually made more work for you--I will not do that again...

The code is working great except for two things:

1. Cells(.Row, FIRST_DAY_COL).Resize(, 31).Clear is clearing everything. I tried to change to ClearContents but this evidently doesn't work with Cells?

- the formatting for the entire sheet is:
-- Font Size = 8
-- Font Type = Aerial
-- Vertical Alignment = Top
-- Horizontal Alignment = different, but I could make it all Left



- I use condition formatting to color the weekends, this might cause a problem?[/VBA]

2. I use =OR(M$9="Sa",M$9="Su") as a Conditional Format to color the weekends and that to is Cleared when the row is changed?

- of course if office dates falls on a weekend day that day would be the office color...

- range M:AQ houses a formula that returns an abbreviated weekday

[Bob Phillips]

xld - I thought it would be easier for you to build the code-- I apologize because it actually made more work for you--I will not do that again...

I can't say I am exactly bothered by it, but when requesting help it is just polite to state it as accurately as possible, and help the helper.



The code is working great except for two things:

1. Cells(.Row, FIRST_DAY_COL).Resize(, 31).Clear is clearing everything. I tried to change to ClearContents but this evidently doesn't work with Cells?

- the formatting for the entire sheet is:
-- Font Size = 8
-- Font Type = Aerial
-- Vertical Alignment = Top
-- Horizontal Alignment = different, but I could make it all Left

2. I use =OR(M$9="Sa",M$9="Su") as a Conditional Format to color the weekends and that to is Cleared when the row is changed?

- of course if office dates falls on a weekend day that day would be the office color...

I don't understand that last sentence.


- range M:AQ houses a formula that returns an abbreviated weekday

ANother version


Option Explicit
Option Base 1

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const START_DATE_COL As Long = 11
Const END_DATE_COL As Long = 12
Const FIRST_DAY_COL As Long = 13
Dim WatchRange As Range
Dim cell As Range
Dim colors As Variant
Dim i As Integer
Dim j As Long
Dim inputs As Variant
Dim col As Long
Dim iStart As Long
Dim iEnd As Long

On Error GoTo ws_exit
Application.EnableEvents = False
Application.ScreenUpdating = False

inputs = Array("Office A", "Office B", "Office C", "Office D", "Office E", "Office F")
colors = Array(3, 4, 5, 6, 7, 8)

Set WatchRange = Range("A2", Cells(Rows.Count, "A").End(xlUp)).Resize(, FIRST_DAY_COL - 1)
If Intersect(Target, WatchRange) Is Nothing Then Exit Sub
With Target
Cells(.Row, FIRST_DAY_COL).Resize(, 31).ClearContents
Cells(.Row, FIRST_DAY_COL).Resize(, 31).Interior.ColorIndex = xlColorIndexNone
If .Column < FIRST_DAY_COL Then
If Cells(.Row, START_DATE_COL).Value <> "" Then
iStart = Day(Cells(.Row, START_DATE_COL))
End If
If Cells(.Row, END_DATE_COL).Value <> "" Then
iEnd = Day(Cells(.Row, END_DATE_COL))
End If
If iStart <> 0 And iEnd <> 0 Then
For i = iStart To iEnd
Cells(.Row, i + FIRST_DAY_COL - 1).Value = i
Next i

i = 0
On Error Resume Next
i = Application.Match(Cells(.Row, "A").Value, inputs, 0)
On Error GoTo 0
If i = 0 Then
Cells(.Row, "A").Resize(, FIRST_DAY_COL - 1).Interior.ColorIndex = xlColorIndexNone
Else
Cells(.Row, "A").Resize(, FIRST_DAY_COL - 1).Interior.ColorIndex = colors(i)
For j = iStart To iEnd
If Weekday(Cells(.Row, START_DATE_COL).Value + j - 1, 2) < 6 Then
Cells(.Row, j + FIRST_DAY_COL - 1).Interior.ColorIndex = colors(i)
End If
Next j
End If
End If
End If

End With
ws_exit:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

[emm]

I don't understand that last sentence.

- range M:AQ houses a formula that returns an abbreviated weekday

Left out the row.
Range M9:AQ9 houses fomula =IF(WEEKDAY(M11)=1,"Su",IF(WEEKDAY(M11)=2,"M",IF(WEEKDAY(M11)=3,"T",IF(WEEKDAY(M11)=4,"W",IF(WEEKDAY(M11)=5,"Th",IF(WEEKDAY(M11)=6,"F",IF(WEEKDAY(M11)=7,"Sa","")))))))

Range M10:AQ10 houses dates formatted Custom "mmm"

Range M11:AQ11 houses formula =M10 and is formatted Custom "d"

So across the top of the Gantt section (M12:AQ100ish) is this--I took a picture of what sheet looked like after plugging in the last version and running each row--should have done this earlier...

The Start Date and End Date used for code are in K & L...

[emm]

xld - I changed the Conditional Format formula to this and it looks like the weekend coloring working (this particular formula is under Nov 5 for Office D):

=AND(OR(Q$9="Sa",Q$9="Su"),Q15="")

There seems to be a pattern of not coloring--look at Fridays and Mondays?

Also when I paste special formats in the Gantt area Excel locks up--I can save and when it reopens everything works?

Any way I think we are close...

[Bob Phillips]

xld - I changed the Conditional Format formula to this and it looks like the weekend coloring working (this particular formula is under Nov 5 for Office D):

=AND(OR(Q$9="Sa",Q$9="Su"),Q15="")

There seems to be a pattern of not coloring--look at Fridays and Mondays?

Also when I paste special formats in the Gantt area Excel locks up--I can save and when it reopens everything works?

Any way I think we are close...

WHy not post your workbook.

[emm]

stand by I'm just figuring out how to do attachments...

[emm]

How do post workbook? Do I have to zip it, when I went through manage attachments it said .xls was no authorized???

[Bob Phillips]

How do post workbook? Do I have to zip it, when I went through manage attachments it said .xls was no authorized???

Yes, you have to zip it.

[emm]

XLD - finally got WinZip downloaded, here is zipped file...

[Bob Phillips]

XLD - finally got WinZip downloaded, here is zipped file...

If a task falls on a weekend, do you want the task colour or the conditional formatting colour?

[emm]

Tasked color...

[Bob Phillips]

Tasked color...

[emm]

Looks like you changed the conditional format formula and a line in the code--works good except for:

- Lets say I copy M12 because I want the weekend color to be different and then I do a paste special "formats" under the date range (M12:AQ24) the file locks up--I can save and then on re-opening I click each office and the row straightens itself out. What do you think is causing the lockup?

- Would it be a major re-write to cause the code to fire on each row from a button click (loop through the rows)?

[Bob Phillips]

Looks like you changed the conditional format formula and a line in the code--works good except for:

Couple of other things as well.




Doesn't lock up here when I do that.

[QUOTE=emm - Would it be a major re-write to cause the code to fire on each row from a button click (loop through the rows)?

Why would you want to do that?

[emm]

It's possible it is my computer--I'm going to try it on another one.


Why would you want to do that?

If I do the aforementioned paste I have to run down the column A and select each office to get the colors straightened out, but if I make sure I set everything up correctly from the get go I shouldn't have to make in formatting changes

[Bob Phillips]

If
I do the aforementioned paste I have to run down the column A and select each office to get the colors straightened out, but if I make sure I set everything up correctly from the get go I shouldn't have to make in formatting changes

I'm asking because you have a nice working code-let, which works well anjd is event driven, which is good in my book, so I am sure we can cater for your request without changing the good bit we already have.

I still don't get so please bear patiently with me. How does changing the the colour of M12 change the weekend colour? Or maybe, better phrased as, what user action would trigger what workbook result?

[emm]

Attached is a paste special "formats" just so you can see what I did?I would do this if I wanted to change the weekend color to blue...



Now, the code is working great--it colors the cells, it does it accurately, and it does it fast. I was doing all this manually for 12 sheets and it was stressing me out!



I'm the one who should be thanking you for your patience. You posted in on this project on 17 Nov and have been here non-stop. So, I thank you greatly, and I?m going to click ?solved? so we both can enjoy the rest of the our weekend?

[Bob Phillips]

Attached is a paste special "formats" just so you can see what I did?I would do this if I wanted to change the weekend color to blue...

Try this.

Select all the cells to be re-formatted, and click the button.

[emm]

That'll work. Thanks a bunch...