question regarding dynamic cell colors

  • Thread starter Thread starter icystorm
  • Start date Start date
I

icystorm

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.
 
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 said:
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

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
Click to expand...
Click to expand...
 
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



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 said:
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

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
Click to expand...
Click to expand...
Click to expand...
 
Thanks, Tom. I appreciate your input. I've also learned that
conditional formatting is possible for this case after all. Thanks.

J


Tom said:
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



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 said:
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

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top