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]

Put the following code in the events of the sheet.
It will perform the subtraction.
The highlight part is missing because I do not understand why of A21 to A17. I understand A21, but why until A17?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 1 Then
        If Target.Count > 1 Then Exit Sub
        Target.Value = Target.Value - 10
        Target.Interior.ColorIndex = 6
    End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

 

[micha3l87]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row[/TD]
[TD]Column A[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]3104[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]3105[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]3106[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]3109[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]3111[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]3114[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]3117[/TD]
[/TR]
</tbody>[/TABLE]


Your code was subtracting 10 from the active cell and turning it yellow not exactly what I was wanting lol
I'm trying to select a row in column A, that value -10 will determine what the range is so in this example Cell A22 is 3117 - 10 so highlight cells A22-A18

Put the following code in the events of the sheet.
It will perform the subtraction.
The highlight part is missing because I do not understand why of A21 to A17. I understand A21, but why until A17?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 1 Then
        If Target.Count > 1 Then Exit Sub
        Target.Value = Target.Value - 10
        Target.Interior.ColorIndex = 6
    End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

 

[kvsrinivasamurthy]

Work sheet event is used.


Range used is A16:A22 . Change if required.
Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim rng As Range, Cel As Range


Set rng = Range("A16:A22")
rng.Interior.Pattern = xlNone


If Not Intersect(Target, rng) Is Nothing Then


    For Each Cel In rng
        If Cel.Address <> Target.Address And Cel < Target And Cel >= Target - 10 Then
        Cel.Interior.Color = 65535
        End If
    Next Cel
End If


End Sub

To paste the code:
Right click on sheet tab --> View code
VBA window opens
Paste the code
Close the window.

 

[DanteAmor]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row[/TD]
[TD]Column A[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]3104[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]3105[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]3106[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]3109[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]3111[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]3114[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]3117[/TD]
[/TR]
</tbody>[/TABLE]


Your code was subtracting 10 from the active cell and turning it yellow not exactly what I was wanting lol
I'm trying to select a row in column A, that value -10 will determine what the range is so in this example Cell A22 is 3117 - 10 so highlight cells A22-A18

I'm sorry, obviously I understood something else.
Try this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim r As Range
    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
        c = Application.Match(Target.Value - 10, r, 1)
        If IsError(c) Then c = r(1).Row Else c = r.Cells(c).Row
        Range(Cells(c, "A"), Cells(Target.Row, "A")).Interior.ColorIndex = 6
    End If
End Sub

 

[micha3l87]

Hey thats awesome! It works how I imagined, sometimes its wrong though, sometimes it highlights over 10

 

[DanteAmor]

I can fix it, but you have to be clearer with the example, explain what data you have, which cell you select, what the macro is doing and what should be the right thing to do.

 

[micha3l87]

The hours in Column A are not 1,2,3,4,5,6 they jump somtimes more then 3 hours at a time.
The macro works perfectly when the selected sell -10 matches a a cell above it. So if you were to select 3270 it would highlight to 3260 perfectly but if there was not a 3260 in the column it will pass it instead of ending in the cell before.

Ideally if 3270 was selected and there isn't a perfect number like 3260 in the list it would stop in this case at 3261 not going over the 10

hopefully that makes sense?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]3258[/TD]
[/TR]
[TR]
[TD]3261[/TD]
[/TR]
[TR]
[TD]3264[/TD]
[/TR]
[TR]
[TD]3265[/TD]
[/TR]
[TR]
[TD]3267[/TD]
[/TR]
[TR]
[TD]3269[/TD]
[/TR]
[TR]
[TD]3270[/TD]
[/TR]
[TR]
[TD]3271[/TD]
[/TR]
[TR]
[TD]3275[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

The hours in Column A are not 1,2,3,4,5,6 they jump somtimes more then 3 hours at a time.
The macro works perfectly when the selected sell -10 matches a a cell above it. So if you were to select 3270 it would highlight to 3260 perfectly but if there was not a 3260 in the column it will pass it instead of ending in the cell before.

Ideally if 3270 was selected and there isn't a perfect number like 3260 in the list it would stop in this case at 3261 not going over the 10

hopefully that makes sense?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]3258[/TD]
[/TR]
[TR]
[TD]3261[/TD]
[/TR]
[TR]
[TD]3264[/TD]
[/TR]
[TR]
[TD]3265[/TD]
[/TR]
[TR]
[TD]3267[/TD]
[/TR]
[TR]
[TD]3269[/TD]
[/TR]
[TR]
[TD]3270[/TD]
[/TR]
[TR]
[TD]3271[/TD]
[/TR]
[TR]
[TD]3275[/TD]
[/TR]
</tbody>[/TABLE]

Their examples are confusing.
In your previous example, you have this:
You select 3117 (A22), 3117-10 = 3107, but the 3107 does not exist, then you select cell A18, which has the number 3106, then you selected a number before 3107.


And now you want a later number.

That is what does not make sense.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row[/TD]
[TD]Column A[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]3104[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]3105[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]3106[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]3109[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]3111[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]3114[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]3117[/TD]
[/TR]
</tbody>[/TABLE]

Your code was subtracting 10 from the active cell and turning it yellow not exactly what I was wanting lol
I'm trying to select a row in column A, that value -10 will determine what the range is so in this example Cell A22 is 3117 - 10 so highlight cells A22-A18

 

[micha3l87]

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