Highlight Cell Range

[micha3l87]

Hey Everyone,

I was hoping someone would be able to help me with what I think will be pretty simple.

When the user clicks a cell in column A, take that cells value subtract 10 and highlight that range in the column.

For example if I click in cell A21 (3113)-(10) = 3103
So highlight A21 to A17

I was going to upload my workbook, but I can't seem to find a attachment button
thank you for any help!

 

[DanteAmor]

Yeah you’re right it should have been A19 in that example

Try this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim r As Range, i As Long
    Set r = Range("A16", Range("A" & Rows.Count).End(xlUp))
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, r) Is Nothing Then
        r.Interior.ColorIndex = xlNone
        For i = Cells(Target.Row, "A") To r.Cells(1).Row Step -1
            If Cells(i, "A") <= Target And Cells(i, "A") >= Target.Value - 10 Then
                Cells(i, "A").Interior.ColorIndex = 6
            End If
        Next
    End If
End Sub