Excel version: Excel 2003 Pro SP2

Overview: I have created an Excel work schedule that displays days of
the week along a single row near the top. (e.g. S M T W T F S [...]
etc.) All additional rows in the array consist of various formulas.
The entire array is 29 rows by 450 columns.

Issue: In the row that displays days of the week, I want to find
*all* cells that contain "S" (for Saturday and Sunday). Then, I
want to change the cell color for those days to gray, and I also want
the entire range in the column beneath each "S" to be shaded gray
as well.

I realize that conditional formatting is not a solution in this case.
I am hoping that someone with VBA experience has already addressed this
issue and developed a viable solution.

The final product should show all weekend columns displayed as gray.
Obviously, I do not want the gray cell coloring to extend beyond the
bottom or top of the array, either.

Thanks kindly for sharing your ideas and ingenuity.

Best Regards,
J

There are only 256 columns in a worksheet. So 29 rows by 450 columns would
be difficult.

You can't progran

for each occupied cell in a row near the top

so why not specify where your data is located and where the row is that
contains the days of the week.

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
Excel version: Excel 2003 Pro SP2

Overview: I have created an Excel work schedule that displays days of
the week along a single row near the top. (e.g. S M T W T F S [...]
etc.) All additional rows in the array consist of various formulas.
The entire array is 29 rows by 450 columns.

Issue: In the row that displays days of the week, I want to find
*all* cells that contain "S" (for Saturday and Sunday). Then, I
want to change the cell color for those days to gray, and I also want
the entire range in the column beneath each "S" to be shaded gray
as well.

I realize that conditional formatting is not a solution in this case.
I am hoping that someone with VBA experience has already addressed this
issue and developed a viable solution.

The final product should show all weekend columns displayed as gray.
Obviously, I do not want the gray cell coloring to extend beyond the
bottom or top of the array, either.

Thanks kindly for sharing your ideas and ingenuity.

Best Regards,
J

Tom,

Thanks for the response. Of course, you're correct. I am only using
225 columns *per sheet*. Sorry for the confusion.

The position of each day of the week changes by design from month to
month. It's dynamic and based on user input.

For example, range B2:C30 should be gray if B2 and C2 = "S". Next
month, C2:D30 may need to be gray if C2 and D2 = "S". It's dynamic by
design.

Thanks greatly for your input. You've helped me before, and it's
always appreciated.

J


Tom Ogilvy wrote:
There are only 256 columns in a worksheet. So 29 rows by 450 columns would
be difficult.

You can't progran

for each occupied cell in a row near the top

so why not specify where your data is located and where the row is that
contains the days of the week.

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
Excel version: Excel 2003 Pro SP2

Overview: I have created an Excel work schedule that displays days of
the week along a single row near the top. (e.g. S M T W T F S [...]
etc.) All additional rows in the array consist of various formulas.
The entire array is 29 rows by 450 columns.

Issue: In the row that displays days of the week, I want to find
*all* cells that contain "S" (for Saturday and Sunday). Then, I
want to change the cell color for those days to gray, and I also want
the entire range in the column beneath each "S" to be shaded gray
as well.

I realize that conditional formatting is not a solution in this case.
I am hoping that someone with VBA experience has already addressed this
issue and developed a viable solution.

The final product should show all weekend columns displayed as gray.
Obviously, I do not want the gray cell coloring to extend beyond the
bottom or top of the array, either.

Thanks kindly for sharing your ideas and ingenuity.

Best Regards,
J

Sub ColorActiveSheet()
Dim cell as Range
for each cell in Range("A2").Resize(1,225)
if lcase(cell.Value) = s then
cell.Resize(29,1).Interior.ColorIndex = 15
end if
Next
End Sub

--
Regards,
Tom Ogilvy



wrote in message
oups.com...
Tom,

Thanks for the response. Of course, you're correct. I am only using
225 columns *per sheet*. Sorry for the confusion.

The position of each day of the week changes by design from month to
month. It's dynamic and based on user input.

For example, range B2:C30 should be gray if B2 and C2 = "S". Next
month, C2:D30 may need to be gray if C2 and D2 = "S". It's dynamic by
design.

Thanks greatly for your input. You've helped me before, and it's
always appreciated.

J


Tom Ogilvy wrote:
There are only 256 columns in a worksheet. So 29 rows by 450 columns
would
be difficult.

You can't progran

for each occupied cell in a row near the top

so why not specify where your data is located and where the row is that
contains the days of the week.

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
Excel version: Excel 2003 Pro SP2

Overview: I have created an Excel work schedule that displays days of
the week along a single row near the top. (e.g. S M T W T F S [...]
etc.) All additional rows in the array consist of various formulas.
The entire array is 29 rows by 450 columns.

Issue: In the row that displays days of the week, I want to find
*all* cells that contain "S" (for Saturday and Sunday). Then, I
want to change the cell color for those days to gray, and I also want
the entire range in the column beneath each "S" to be shaded gray
as well.

I realize that conditional formatting is not a solution in this case.
I am hoping that someone with VBA experience has already addressed
this
issue and developed a viable solution.

The final product should show all weekend columns displayed as gray.
Obviously, I do not want the gray cell coloring to extend beyond the
bottom or top of the array, either.

Thanks kindly for sharing your ideas and ingenuity.

Best Regards,
J

Thanks, Tom. I appreciate your input. I've also learned that
conditional formatting is possible for this case after all. Thanks.

J


Tom Ogilvy wrote:
Sub ColorActiveSheet()
Dim cell as Range
for each cell in Range("A2").Resize(1,225)
if lcase(cell.Value) = s then
cell.Resize(29,1).Interior.ColorIndex = 15
end if
Next
End Sub

--
Regards,
Tom Ogilvy



wrote in message
oups.com...
Tom,

Thanks for the response. Of course, you're correct. I am only using
225 columns *per sheet*. Sorry for the confusion.

The position of each day of the week changes by design from month to
month. It's dynamic and based on user input.

For example, range B2:C30 should be gray if B2 and C2 = "S". Next
month, C2:D30 may need to be gray if C2 and D2 = "S". It's dynamic by
design.

Thanks greatly for your input. You've helped me before, and it's
always appreciated.

J


Tom Ogilvy wrote:
There are only 256 columns in a worksheet. So 29 rows by 450 columns
would
be difficult.

You can't progran

for each occupied cell in a row near the top

so why not specify where your data is located and where the row is that
contains the days of the week.

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
Excel version: Excel 2003 Pro SP2

Overview: I have created an Excel work schedule that displays days of
the week along a single row near the top. (e.g. S M T W T F S [...]
etc.) All additional rows in the array consist of various formulas.
The entire array is 29 rows by 450 columns.

Issue: In the row that displays days of the week, I want to find
*all* cells that contain "S" (for Saturday and Sunday). Then, I
want to change the cell color for those days to gray, and I also want
the entire range in the column beneath each "S" to be shaded gray
as well.

I realize that conditional formatting is not a solution in this case.
I am hoping that someone with VBA experience has already addressed
this
issue and developed a viable solution.

The final product should show all weekend columns displayed as gray.
Obviously, I do not want the gray cell coloring to extend beyond the
bottom or top of the array, either.

Thanks kindly for sharing your ideas and ingenuity.

Best Regards,
J