Changing the Font colour of selected cells columns and rows

Nauticus66

New Member
Joined
Jun 13, 2018
Messages
2
Hi Guys,

First time on the site.

We found the following code online:
Code:
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:
Code:
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

Well-known Member
Joined
Oct 5, 2015
Messages
796
Office Version
2007
Platform
Windows
This modification to your code does it (It can be cleaned up somewhat but it works as is).:
Code:
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
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
796
Office Version
2007
Platform
Windows
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.
 

Forum statistics

Threads
1,082,269
Messages
5,364,150
Members
400,783
Latest member
sambills

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top