This is probably the easiest way to highlight the active cell and it requires no VBA or even conditional formatting.

Look at this picture,

And this one,

And this one.

See the difference? By changing the borders, the active cell becomes surrounded by color. In fact, if you use double lines or thicker lines, the color becomes even more visible. The best colors to highlight seem to be medium colors such as Teal or Blue, but darker colors also work (don’t go for the grays though) and they usually show as “black” when printing anyway.

Nice. But before you start trying this out and changing all of the Borders…

Automatic As Default
This took a while to figure out – The default setting for Gridline colors is “Automatic” (the pale gray lines you see when you open a new workbook) and the default setting for Border is Automatic also where they will show as “black”.

While the Borders are in the default setting, you can change the Gridlines color by going to Tools, Options, View at the top menu and the Borders will also change to the same color. Even if you untick the Gridlines checkbox to hide them, the Borders will still show as the new color.

This is a sheet wide change. To do this with VBA is simple enough. (There appears to be an RGB version too though I’ve not tried it yet, this method uses the same color index as Interior and Font colors)

Sub ChangeGridlineColorSheet()
On Error Resume Next
ActiveWindow.GridlineColorIndex = 3 ' change color to suit
End Sub
So for every sheet in a workbook, we can use this…

Sub ChangeGridlineColorBook()
On Error Resume Next
Dim ws As Worksheet, tSheet As Worksheet
Set tSheet = ActiveSheet
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
ActiveWindow.GridlineColorIndex = 3 ' change color to suit
Next
tSheet.Select
End Sub
I’ve even applied this to an application class event at work, running the above subroutine from a Workbook Open event. The advantage is any workbook I open, mine or not, changes the Gridline color to suit. The downside is it takes a little longer to open, lucky the workbooks at my company tend be to rather small. You should give the implications some serious thought before you try it though. (Don’t use Change or Selection Change for an application wide class event, this interferes with the ability to paste)

But, how about if the Borders are not set as Automatic? Is there an easy way to change the color of all the Borders and still retain existing line weight and patterns?

Sure. Run this code for a selection.

Sub ChangeBorderColorSelection()
On Error Resume Next
Dim c As Range, i As Integer
If TypeName(Selection) <> "Range" Then Exit Sub
Application.ScreenUpdating = False
For Each c In Selection
With c
For i = 1 To 8
If .Borders(i).ColorIndex <> -4142 Then _
.Borders(i).ColorIndex = 3
Next
End With
Next
Application.ScreenUpdating = True
End Sub
You can adapt this code to work with sheets and books, but consider that really big files make take a long time as the code has to run through each and every cell. In my case, just a minute or two per book so no big deal. (Don’t forget to save first just in case!)

For Sheets,

Sub ChangeBorderColorSheet()
On Error Resume Next
Dim c As Range, tRange As Range, i As Integer
Application.ScreenUpdating = False
Set tRange = Selection
ActiveSheet.UsedRange.Select
For Each c In Selection
With c
For i = 1 To 8
If .Borders(i).ColorIndex <> -4142 Then _
.Borders(i).ColorIndex = 3 ' Change color here
Next
End With
Next
tRange.Select
Application.ScreenUpdating = True
End Sub

And for books,

Sub ChangeBorderColorBook()
On Error Resume Next
Dim ws As Worksheet, tSheet As Worksheet
Dim c As Range, tRange As Range, i As Integer
Set tSheet = ActiveSheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Set tRange = Selection
ws.UsedRange.Select
For Each c In Selection
With c
For i = 1 To 8
If .Borders(i).ColorIndex <> -4142 Then _
.Borders(i).ColorIndex = 3 ' Change color here
Next
End With
Next
tRange.Select
Next
Application.ScreenUpdating = True
tSheet.Select
End Sub
And each and every book in a folder? Well that can be done too. But it’s time to watch the Sunday night movie

Sometime later: Ah, the movie is boring. Use this code to get the color index (some colors are repeated)

Sub ShowColorIndex()
On Error Resume Next
Workbooks.Add
Do While ActiveCell.Row < 57
With ActiveCell
.Interior.ColorIndex = .Row
.Offset(, 1) = .Row
.Offset(1).Select
End With
Loop
Cells(1, 1).Select
End Sub

No related posts.

Related posts brought to you by Yet Another Related Posts Plugin.