Highlighting/shading cell below cell

[Jimboexcel]

Hi

I want to be able to highlight a cell below another cell. For example, if I highlight A2 in the table below, I want A15 to be highlighted also, regardless of the value contained in it. How to do this?

Thanks

Jimbo

<colgroup><col style="width:51pt" span="5" width="69"> </colgroup><tbody>
[TD="class: xl65, width: 69"]Y1[/TD]
[TD="class: xl65, width: 69"]Y2[/TD]
[TD="class: xl65, width: 69"]Y3[/TD]
[TD="class: xl65, width: 69"]Y4[/TD]
[TD="class: xl65, width: 69"]Y5[/TD]

[TD="align: right"]11.939[/TD]
[TD="align: right"]9.98[/TD]
[TD="align: right"]15.339[/TD]
[TD="align: right"]15.816[/TD]
[TD="align: right"]9.013[/TD]

[TD="align: right"]10.706[/TD]
[TD="align: right"]10.426[/TD]
[TD="align: right"]8.204[/TD]
[TD="align: right"]12.092[/TD]
[TD="align: right"]8.685[/TD]

[TD="align: right"]6.644[/TD]
[TD="align: right"]9.996[/TD]
[TD="align: right"]8.993[/TD]
[TD="align: right"]6.013[/TD]
[TD="align: right"]7.569[/TD]

[TD="align: right"]13.187[/TD]
[TD="align: right"]4.35[/TD]
[TD="align: right"]9.001[/TD]
[TD="align: right"]12.922[/TD]
[TD="align: right"]8.446[/TD]

[TD="align: right"]8.433[/TD]
[TD="align: right"]9.473[/TD]
[TD="align: right"]10.991[/TD]
[TD="align: right"]7.072[/TD]
[TD="align: right"]8.802[/TD]

[TD="align: right"]9.21[/TD]
[TD="align: right"]12.695[/TD]
[TD="align: right"]9.888[/TD]
[TD="align: right"]11.502[/TD]
[TD="align: right"]9.981[/TD]

[TD="align: right"]7.961[/TD]
[TD="align: right"]13.309[/TD]
[TD="align: right"]15.664[/TD]
[TD="align: right"]10.21[/TD]
[TD="align: right"]11.219[/TD]

[TD="align: right"]14.921[/TD]
[TD="align: right"]10.84[/TD]
[TD="align: right"]13.651[/TD]
[TD="align: right"]4.476[/TD]
[TD="align: right"]12.045[/TD]

[TD="align: right"]12.529[/TD]
[TD="align: right"]10.831[/TD]
[TD="align: right"]7.978[/TD]
[TD="align: right"]6.439[/TD]

[TD="align: right"]10.123[/TD]
[TD="align: right"]11.373[/TD]
[TD="align: right"]10.123[/TD]
[TD="align: right"]8.514[/TD]
[TD="align: right"]17.652[/TD]

[TD="align: right"]11.407[/TD]
[TD="align: right"]2.805[/TD]
[TD="align: right"]8.754[/TD]
[TD="align: right"]11.201[/TD]
[TD="align: right"]13.622[/TD]

[TD="align: right"]12.157[/TD]
[TD="align: right"]9.088[/TD]
[TD="align: right"]7.525[/TD]
[TD="align: right"]9.456[/TD]

[TD="align: right"]10.829[/TD]
[TD="align: right"]7.712[/TD]
[TD="align: right"]8.169[/TD]
[TD="align: right"]8.832[/TD]
[TD="align: right"]12.594[/TD]

[TD="align: right"]12.368[/TD]
[TD="align: right"]7.645[/TD]
[TD="align: right"]11.062[/TD]
[TD="align: right"]7.773[/TD]
[TD="align: right"]14.905[/TD]

[TD="align: right"]10.052[/TD]
[TD="align: right"]9.431[/TD]
[TD="align: right"]16.623[/TD]
[TD="align: right"]13.348[/TD]
[TD="align: right"]9.074[/TD]

[TD="align: right"]5.036[/TD]
[TD="align: right"]9.125[/TD]
[TD="align: right"]17.502[/TD]
[TD="align: right"]7.753[/TD]
[TD="align: right"]5.849
[/TD]

[TD="align: right"]14.799[/TD]
[TD="align: right"]8.543
[/TD]
[TD="align: right"]10.066[/TD]
[TD="align: right"]10.695[/TD]

[TD="align: right"]10.478[/TD]
[TD="align: right"]9.377[/TD]
[TD="align: right"]14.934[/TD]
[TD="align: right"]9.881[/TD]
[TD="align: right"]10.626[/TD]

[TD="align: right"]13.859[/TD]
[TD="align: right"]8.998[/TD]
[TD="align: right"]6.635[/TD]
[TD="align: right"]7.851[/TD]
[TD="align: right"]11.352[/TD]

[TD="align: right"]12.355[/TD]
[TD="align: right"]8.591[/TD]
[TD="align: right"]11.282[/TD]
[TD="align: right"]7.891[/TD]
[TD="align: right"]6.379
[/TD]

[TD="align: right"]13.809[/TD]
[TD="align: right"]13.92[/TD]
[TD="align: right"]14.678[/TD]
[TD="align: right"]10.383[/TD]
[TD="align: right"]9.448[/TD]

[TD="align: right"]7.443[/TD]
[TD="align: right"]10.938[/TD]
[TD="align: right"]11.432[/TD]
[TD="align: right"]11.081[/TD]
[TD="align: right"]11.793[/TD]

[TD="align: right"]8.855[/TD]
[TD="align: right"]13.501[/TD]
[TD="align: right"]11.159[/TD]
[TD="align: right"]10.475[/TD]
[TD="align: right"]8.668[/TD]

[TD="align: right"]9.461[/TD]
[TD="align: right"]13.967[/TD]
[TD="align: right"]15.552[/TD]
[TD="align: right"]8.399[/TD]
[TD="align: right"]14.824[/TD]

[TD="align: right"]2.439[/TD]
[TD="align: right"]9.677[/TD]
[TD="align: right"]13.062[/TD]
[TD="align: right"]10.532[/TD]
[TD="align: right"]10.43[/TD]

[TD="align: right"]5.527[/TD]
[TD="align: right"]8.814[/TD]
[TD="align: right"]11.876[/TD]
[TD="align: right"]10.244[/TD]
[TD="align: right"]5.986[/TD]

[TD="align: right"]9.921[/TD]
[TD="align: right"]11.262[/TD]
[TD="align: right"]10.08[/TD]
[TD="align: right"]6.54[/TD]
[TD="align: right"]11.994[/TD]

[TD="align: right"]11.814[/TD]
[TD="align: right"]9.56[/TD]
[TD="align: right"]3.799[/TD]
[TD="align: right"]4.517[/TD]
[TD="align: right"]13.855[/TD]

[TD="align: right"]13.403[/TD]
[TD="align: right"]9.813[/TD]
[TD="align: right"]7.583[/TD]
[TD="align: right"]1.776[/TD]
[TD="align: right"]12.35[/TD]

[TD="align: right"]10.819[/TD]
[TD="align: right"]4.918[/TD]
[TD="align: right"]11.326[/TD]
[TD="align: right"]1.993[/TD]
[TD="align: right"]9.272
[/TD]

</tbody>

 

[Jimboexcel]

In other words, if there is a value in A2, highlight that value and also the value in A15, or whichever cell, can this be done?

 

[Perpa]

Jimboexcel,
Welcome to the Forum. See if the following code does what you want.

Put the code below in a worksheet module on the your worksheet.
To do that just right click on the sheet tab, select 'view code' to open the VB Editor, change from
'General' to 'Worksheet', paste the code below right over the default two lines of code shown, then
close the VB Editor, and Save your workbook as macro enabled. When you open the workbook you
may have to enable macros depending on the version of Excel you have.

Then when you select a cell, the selected cell and the cell 13 rows below (same column) will be highlighted yellow. If you select another cell in that range, the fill in the first two cells will be cleared, and the selected cell and the offset cell will be highlighted.

You can change the range and fill color to suit, I chose 'A1:G100' and 'yellow' for this example.
If you select more than one cell, a corresponding number of cells is selected 13 rows below.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim thecell As Range
    Range("A1:G100").Interior.ColorIndex = xlNone   'Clears previous selection fill
    If (Not IsEmpty(thecell)) Then
        With thecell
        ' yellow
            .Interior.Color = RGB(255, 253, 56)
            .Offset(13, 0).Interior.Color = RGB(255, 253, 56)
        End With
     End If
End Sub

Here are a few other RGB color designations:
RGB(250, 60, 70) 'Red
RGB(205, 254, 204) 'Green
RGB(78, 152, 178) 'Blue
RGB(204, 192, 218) 'Purple

Perpa

 

[Jimboexcel]

Thanks Perpa for your reply and instructions. However after following, there is a problem. It says 'run time error 91. Object variable or with block variable not set.' when I click on a cell. When I click debug, it points to the line .Interior.Color = RGB(255, 253, 56)

 

[Perpa]

Jimboexcel,
Replace the previous code with this code, it replaces the variable 'thecell' with 'Selection'.
That should take care of the problem. Let me know if that doesn't fix the error.
Perpa

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("A1:G100").Interior.ColorIndex = xlNone   'Clears previous selection fill
    If (Not IsEmpty(Selection)) Then
        With Selection
        ' yellow
            .Interior.Color = RGB(255, 253, 56)
            .Offset(13, 0).Interior.Color = RGB(255, 253, 56)
        End With
     End If
End Sub

 

[Jimboexcel]

That works well now. Thanks Perpa, appreciate your help.

 

[Perpa]

That works well now. Thanks Perpa, appreciate your help.

Glad that worked...You are welcome.