Excel highlight current cell and everry 7th cell back

[sdh966]

I am currently working with a file that contains dates in chronological order in column A and column B contains a dollar value. I would like to highlight the current cell in a color where I put the cursor and then highlight every 7th cell(week) back. For example, if I put my cursor on cell A11, I would like that cell to highlight yellow and then highlight every 7th cell back in the same color. I am looking at a weekly trends. I have the code, which highlights the entire row where I put the cursor but cannot get it to just highlight the cell and every 7th cell back. here is the code which highlights the entire row where the cursor is.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub

Application.ScreenUpdating = False
' Clear the color of all the cells
Target.Parent.Cells.Interior.ColorIndex = 0
With Target
' Highlight the entire row and column that contain the active cell
.EntireRow.Interior.ColorIndex = 10

End With
Application.ScreenUpdating = True
End Sub

[dmcgov]

change this:

.EntireRow.Interior.ColorIndex = 10

to this:

Target.Interior.ColorIndex = 10

that will highlight the cell and not the row.

working on last 7th item

[sdh966]

Thanks so much. Your code does exactly what I need for current cell. Thank you for working on highlighting also every 7th cell back. I will wait for your valuable answer.

[dmcgov]

ok, i tested this and it works as you have described:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Dim x, y, z
x = Split(Target.Address, "$")
y = x(2)
Application.ScreenUpdating = False
' Clear the color of all the cells
Target.EntireColumn.Interior.ColorIndex = 0
    With Target
    ' Highlight the entire row and column that contain the active cell
        Target.Interior.ColorIndex = 10
    End With
    For z = y To 1 Step -7
        Range(x(1) & z).Interior.ColorIndex = 10
    Next z
Application.ScreenUpdating = True
End Sub

[sdh966]

Awesome. Works great. You know there is always a but...LOL. I currently have the row headers in ROW3 and would those not to be highlighted in the color ad remain as they are. Can you have the every 7th cell back stop at row 4 and not highlight beyond row 4?

[dmcgov]

that is why we are here, always lots of buts, LOL

i tidied up the code a bit, so here is what works:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Dim x, y, z, a
x = Split(Target.Address, "$")
If x(1) <> "A" Then Exit Sub
y = x(2)
Application.ScreenUpdating = False
' Clear the color of all the cells
Target.EntireColumn.Interior.ColorIndex = 0
    ' Highlight the entire row and column that contain the active cell
        Target.Interior.ColorIndex = 10
    For z = y To 4 Step -7
        Range(x(1) & z).Interior.ColorIndex = 10
    Next z
Application.ScreenUpdating = True
End Sub

[sdh966]

Awesome. Works great. You know there is always a but...LOL. I currently have the row headers in ROW3 and would not like those not to be highlighted in the color and remain as they are. Can you have the code stop at row 3 and not highlight beyond row 3? Meaning not highlight rows 1-3.

[dmcgov]

if this satisfies your needs, please mark the thread as solved and if your feeling generous, you can click on my "Add Reputation" link.

my solution is meeting your needs in that it won't highlight A1:A3

[dmcgov]

updated the solution