macro

[chazzerman01]

does anyone know a macro code that will highlight the whole row that the cursor is on. im using it to make it easier to read values in a table

 

[KOKOSEK]

Small tweak:

Public OldTarget As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cell As Range
    On Error Resume Next
    For Each cell In Range(Cells(OldTarget.Row, "A"), Cells(OldTarget.Row, "O"))
        cell.Interior.Color = Cells(100000, cell.Column).Interior.Color
        If cell.Interior.Color = 16777215 Then
            With cell.Interior
                .Pattern = xlNone
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        End If
    Next cell
  
    Range(Cells(Target.Row, "A"), Cells(Target.Row, "O")).Interior.Color = RGB(255, 255, 0)
    Set OldTarget = Target
End Sub

 

[jasonb75]

I just tried it and it didn't work

Care to elaborate on that? It works for everyone else, just maybe less preferable if you focus on aesthetics.

 

[KOKOSEK]

Absolutely agree.
It a huge difference between 'does not work' and 'does not work like I want to (dim Because As String NOT OPTIONAL)'

 

[Peter_SSs]

You might like to consider this approach as it will not destroy any existing colour in the worksheet. Another advantage is that if the worksheet already has conditional formatting, this new CF will temporarily over-ride it so the selected row/column is clearly visible. (If other CF is added after the CF described here, it may hinder this & a slight adjustment could be needed. Post back if further information about this is required)

1. Select the whole worksheet (by clicking the box at the top left at the intersection of the column labels and row labels). We could select a lesser range but applying it to the whole worksheet doesn't seem to make files too big or slow in my experience.

2. Use Conditional Formatting -> New rule.. -> Use a formula to determine ... -> Format values where this formula is true: =CELL("row")=ROW() -> Format... -> On the Fill tab select the colour you want** for the entire row -> Ok -> Ok

3. With the whole sheet still selected go to Conditional Formatting -> New rule.. -> Use a formula to determine ... -> Format values where this formula is true: =CELL("col")=COLUMN() -> Format... -> On the Fill tab select the same colour -> Ok -> Ok (This step was to include highlighting the column as well)

4. Right click the sheet name tab and choose 'View Code'

5. Copy and Paste the code below into the main right hand pane that opens at step 4.

6. Close the Visual Basic window & test.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.ScreenUpdating = True
End Sub

** When choosing a colour, better to choose a colour that is not already used on the worksheet. On the Fill tab if you go to 'More Colors...' it would be easy to make a colour unique for your sheet.

A further advantage of this method it that even with the vba code involved, Excel's 'Undo' function is preserved.

 

[chazzerman01]

Care to elaborate on that? It works for everyone else, just maybe less preferable if you focus on aesthetics.

its started doing it with kokoseks one as well now, whenever I click on a cell it just come up on the macro ambiguous name detected: worksheet_selectionchange

 

[KOKOSEK]

Change it into:

Public OldTarget As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cell As Range
If Not Intersect(Target, [A:O]) Is Nothing Then   ' change here for which range of columns macro has to react
    On Error Resume Next
    For Each cell In Range(Cells(OldTarget.Row, "A"), Cells(OldTarget.Row, "O"))
        cell.Interior.Color = Cells(cell.Row + 1, cell.Column).Interior.Color
        If cell.Interior.Color = 16777215 Then
            With cell.Interior
                .Pattern = xlNone
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        End If
    Next cell
  
    Range(Cells(Target.Row, "A"), Cells(Target.Row, "O")).Interior.Color = RGB(255, 255, 0)
    Set OldTarget = Target
End If
End Sub

Now it reacts only for selection in columns A:O

 

[chazzerman01]

it doesn't work again it just says ambiguous name detected: worksheet_selectionchange

 

[KOKOSEK]

The ambiguous name error usually means that you have two subs within a module of the same name.
As I see above highlighted line there is end of the same sub (Set OldTarget=Target)

 

[Peter_SSs]

it just says ambiguous name detected: worksheet_selectionchange

You can only have one worksheet_selectionchange code in your worksheet.

Have you tried the suggestion in post 24? It does have a number of advantages over the other methods.
I would suggest that you start a brand new workbook (so that no other codes can disrupt the trial) and give it a go.

If successful then you can set it up in your main workbook. If you have other Worksheet_SelectionChange code to do other tasks beside this highlighting then the codes will need to be combined. If that is the case and you need help with it, you will need to post your existing Worksheet_SelectionChange code.

 

[chazzerman01]

the row above the highlighted one says 'Public OldTarget As Range'