matching cell color based on cells above

[sashazaliz]

I'm trying to match the exact cell colors in a specific named range of cells to the adjacent cells 18 rows below. I thought the following code would certainly work but when I recalculate the worksheet the cells below don't change colors to match the ones above. What's wrong exactly? Please help!

Private Sub Worksheet_Calculate()
Dim cell As Range
For Each cell In Me.Range("color")
cell.Offset(18, 0).Interior.ColorIndex = _
cell.Interior.ColorIndex
Next
End Sub

 

[repairman615]

Hello,

Try:



<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()<br><SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range<br><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Range("color")<br>    cell.Offset(18, 0).Interior.ColorIndex = _<br>    cell.Interior.ColorIndex<br>    <SPAN style="color:#00007F">Next</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[sashazaliz]

thanks for replying. For some reason the cells still don't change color after I press F9 to recalculate the sheet. any ideas on why this might be?

 

[repairman615]

Not sure,

It also doesn't when I try the same.

Type =1+1 into any cell and press enter.

It should work then.

I also noticed some colors were slightly off,

try this instead:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()<br><SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range<br><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Range("color")<br>    cell.Offset(18, 0).Interior.Color = _<br>    cell.Interior.Color<br>    <SPAN style="color:#00007F">Next</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[repairman615]

Ahh,

CTRL+ALT+F9 worked for me.

-Jeff

 

[sashazaliz]

yep it worked for me as well. thanks so much!

 

[repairman615]

Glad to help!


If you have any trouble with the colors not being exact, try the code in post number 4.

-Jeff

 

[sashazaliz]

yes I'm using the latter code since it's imperative the colors are an exact match. again, thank you for your help. really appreciate it