Shading a cell relative to three other cells & repeat

[Emerogork via OfficeKB.com]

I wish to shade to grey, or change to white, cells in a chart from G8 to BR28
relative to variable information in columns B and D and compared to preset
values in row 7. I don't believe that this code can be within the cell as the
user will be entering text there at some other time. I think this will
probably be updated using the Worksheet_Change event but if anyone has a
better idea, I am open for it.

In pseudo-pseudo code:

For the first row:
If ((B8 <= G7) And (D8 >= G7)) Then (G8 through J8).shading = grey (else
shading = white.)
If ((B8 <= K7) And (D8 >= K7)) Then (K8 through N8).shading = grey (else
shading = white.)
If ((B8 <= O7) And (D8 >= O7)) Then (O8 through R8).shading = grey (else
shading = white.)
If ((B8 <= S7) And (D8 >= S7)) Then (S8 through V8).shading = grey (else
shading = white.)
...
This continues to shade horizontally until G8 - BR8 have been shaded or
not

And similar for the second row:
If ((B9 <= G7) And (D9 >= G7)) Then (G9 through J9).shading = grey (else
shading = white.)
If ((B9 <= K7) And (D9 >= K7)) Then (K9 through N9).shading = grey (else
shading = white.)
If ((B9 <= O7) And (D9 >= O7)) Then (O9 through R9).shading = grey (else
shading = white.)
If ((B9 <= S7) And (D9 >= S7)) Then (S9 through V9).shading = grey (else
shading = white.)

and this will continue for rows 8 - 28.

If this requires 1280 lines of code with minute changes in each line, then so
be it but I believe that some fancy looping can be done here. I just don't
know the VBE code to loop it.

I hope my comparison logic is correct here (-:

 

[Ian]

I think I have this right. On rows 8 to 28 you want to compare the values in
B&D with the values in row 7, every 4th column starting at column G (ie G,
K, O, S, W, AA etc)

One option is to use conditional formatting.

In G8 go to conditional formatting and enter Formula is
=AND($B8<=$G$7,$D8>=$G$7).
Select format | patterns and click a suitable grey.
Copy this formula across row 8 to column K (if you already have data in the
sheet, you can use Paste Special | Formats)
Edit the condition at K8 to change the references from G to K, but leave
everything else the same.
Copy across to column O and edit O8.
Continue this process across row 8 until you reach BR8
Select G8:BR8 and copy the row down to row 28

 

[Emerogork via OfficeKB.com]

So cool, thanks. It looks real easy once you have the answer (-:

I think I have this right. On rows 8 to 28 you want to compare the values in
B&D with the values in row 7, every 4th column starting at column G (ie G,
K, O, S, W, AA etc)

One option is to use conditional formatting.

In G8 go to conditional formatting and enter Formula is
=AND($B8<=$G$7,$D8>=$G$7).
Select format | patterns and click a suitable grey.
Copy this formula across row 8 to column K (if you already have data in the
sheet, you can use Paste Special | Formats)
Edit the condition at K8 to change the references from G to K, but leave
everything else the same.
Copy across to column O and edit O8.
Continue this process across row 8 until you reach BR8
Select G8:BR8 and copy the row down to row 28

I wish to shade to grey, or change to white, cells in a chart from G8 to
BR28

[quoted text clipped - 48 lines]

I hope my comparison logic is correct here (-:

 

[Ian]

No problem. Glad it does what you want.

--
Ian
--

So cool, thanks. It looks real easy once you have the answer (-:

I think I have this right. On rows 8 to 28 you want to compare the values
in
B&D with the values in row 7, every 4th column starting at column G (ie G,
K, O, S, W, AA etc)

One option is to use conditional formatting.

In G8 go to conditional formatting and enter Formula is
=AND($B8<=$G$7,$D8>=$G$7).
Select format | patterns and click a suitable grey.
Copy this formula across row 8 to column K (if you already have data in
the
sheet, you can use Paste Special | Formats)
Edit the condition at K8 to change the references from G to K, but leave
everything else the same.
Copy across to column O and edit O8.
Continue this process across row 8 until you reach BR8
Select G8:BR8 and copy the row down to row 28

I wish to shade to grey, or change to white, cells in a chart from G8 to
BR28

[quoted text clipped - 48 lines]

I hope my comparison logic is correct here (-: