Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Shading a cell relative to three other cells & repeat
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 (-: -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Shading a cell relative to three other cells & repeat
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 -- Ian -- "Emerogork via OfficeKB.com" <u36894@uwe wrote in message news:778565f4fed5d@uwe... 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 (-: -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Shading a cell relative to three other cells & repeat
So cool, thanks. It looks real easy once you have the answer (-:
Ian wrote: 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 (-: -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Shading a cell relative to three other cells & repeat
No problem. Glad it does what you want.
-- Ian -- "Emerogork via OfficeKB.com" <u36894@uwe wrote in message news:778a853608ac0@uwe... So cool, thanks. It looks real easy once you have the answer (-: Ian wrote: 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 (-: -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
repeat cell values into other cells x times | Excel Discussion (Misc queries) | |||
How do I count cells with a specific cell shading? | Excel Worksheet Functions | |||
Cell shading in linked cells | Excel Worksheet Functions | |||
Conditional Cell Shading (based on the shading of other cells) | Excel Worksheet Functions | |||
Apply cell shading based on adjacent cells in EXCEL | Excel Worksheet Functions |