Changing the Font colour of selected cells columns and rows

[Nauticus66]

Hi Guys,

First time on the site.

We found the following code online:

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
Cells.Font.ColorIndex = 0
With Target
    ' Highlight the entire row and column that contain the active cell
    .EntireRow.Font.ColorIndex = 3
    .EntireColumn.Font.ColorIndex = 5
End With
Application.ScreenUpdating = True
End Sub

We tried to adapt this to be a macro to be enable on a hotkey:

Public Sub Highlight()  Application.ScreenUpdating = False
  ' Clear the color of all the cells
  ActiveSheet.Cells.Font.ColorIndex = 0
  With ActiveCell
      ' Highlight the entire row and column that contain the active cell
      .EntireRow.Font.ColorIndex = 3
      .EntireColumn.Font.ColorIndex = 5
  End With
  Application.ScreenUpdating = True
End Sub

Unfortunately this doesn't work when multiple cells across columns/rows are selected like the first one.
When I tried to ammend it to be selection.entirerow, it threw up an object error.

Can anyone help me resolve this please?

Thanks, Nauticus66

 

[jmacleary]

This modification to your code does it (It can be cleaned up somewhat but it works as is).:

Public Sub Highlight()Application.ScreenUpdating = False
  ' Clear the color of all the cells
  ActiveSheet.Cells.Font.ColorIndex = 0
  
     Dim cel As Range
    Dim selectedRange As Range


    Set selectedRange = Application.Selection


    For Each cel In selectedRange.Cells
  With cel
      ' Highlight the entire row and column that contain the active cell
      ActiveSheet.Rows(cel.Row).Font.ColorIndex = 3
     ActiveSheet.Columns(cel.Column).Font.ColorIndex = 3
  End With
    Next cel
  Application.ScreenUpdating = True
End Sub

 

[Nauticus66]

Hi jmaclearly,

Perfect

Thank You

Still struggling with 'Dim' VBA codes.

Nauticus66

 

[jmacleary]

You're welcome. Just to explain a bit, What the DIM statement tells VBA is what object you want to use the name for - in this case I told it that cel and selectedRange would be a range object. The set selectedRange... statement then sets the selectedRange object to have all the current selected cells. The FOR each ..... statement then selects each cell in turn from that original range into the cel object. Looking at the code I wrote, I've put in a surplus 'With cel' and 'end with' - you could delete these lines if you want.
Hope that makes things a little clearer, but the best thing to do is find and explore the object model map - in vba help, search for 'object model map' and follow the links - it will show you the huge range of objects you can play with.
Good luck.