YES !! You Can Highlight over Cond. Format

[hilyete]

This has been bothering me for some time and nothing posted so far has solved this dilemma for those of us who are using conditional formatting to create the on/off banding on a sheet, but also want to have the "crosshair" highlighter that lights up both the column and the row.

If you try any of the codes out there, either they will not override the banding, meaning it only highlights between the "banded" rows, and creates a very jumbled and confusing look, or they delete the conditional formatting completely. Another problem is that, even if you could get the highlighting to work, it doesn't look transparent, where you can see the cell lines, and the banding (since it disappears) underneath.

Mr. Moala has a proceedure that will produce the desired effect, but it only works with the directional keys, which means it won't highlight the row/column you click on with the mouse. It also uses a rewriting of the OnKey proceedures, which isn't a problem, unless you close out the program somehow, without resetting the OnKeys to their default... then, Excel may refuse to load until you do a "system restore" to an earlier time as I discovered personally.

So, I finally came up with this. It allows you to keep your conditional formatting bands, and "appears" to have transparency. It works both with the click of the mouse and with the directional keys, and it doesn't mess with the API or OnKeys proceedures. Due to the way it works, it's probably best employed on sheets that have more limited rows and columns.

I have set this example to work only on a range from A1 to F13. Select that area and use conditional formatting to set the banding by using the formula =Mod(row(),2)=1. Set the format to the light gray (#15). Its the 4th one down on the last column of the color chart (8,4).

To create the apparent transparency, go to "Tools", Options >> Colors and click on the bright yellow (3,4). Click on "modify" >> "Custom" and set the sliders to R=255, G=255, B=101.

Now click on the Bright Pink (1,4) and change it to R=209,G=226,B=84

Finally, adjust the gray that you set for the banding to make it a little lighter. Change its setting to R=218, G=218, B=218.

Put the code below in the selection_change event procedure for the worksheet you're using, and then watch how it works right over the banding without losing the banding, and giving the impression that you can see through the highlight. It's pretty cool. If you want to use if for larger areas, you'll have to adjust the code to fit. If someone can see what I did and make it work better, then please do.

My code resets the cell borders (to look transparent) each time you select a cell, but there is no need for that. You can do the same thing once to your work area and then remove that code.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim SelJoint As Range
Dim SelCol As Range
Dim SelRow As Range
Dim myRow As Long
Dim myCol As Long
Dim i As Integer
Dim j As Integer

Application.ScreenUpdating = False

Cells.Interior.ColorIndex = xlNone

With Range("A1:F13").Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 15
    End With
With Range("A1:F13").Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 15
    End With
With Range("A1:F13").Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 15
    End With
    

For i = 1 To 13
    If Cells(i, 1).Row Mod 2 = 1 Then
       For j = 1 To 6
            If Cells(i, j).FormatConditions(1).Interior.ColorIndex = 7 Then
            Cells(i, j).FormatConditions(1).Interior.ColorIndex = 15
            End If
        Next j
    End If
Next i

myRow = Target.Row
myCol = Target.Column

Set SelRow = Range("A" & myRow, "F" & myRow)
Set SelCol = Range(Cells(1, myCol), Cells(13, myCol))
Set SelJoint = Union(SelRow, SelCol)

SelJoint.Interior.ColorIndex = 6 'highlight color

' If the row is an Odd number row:
'Change column to highlighted
If Target.Row Mod 2 = 1 Then
For i = 1 To 13
On Error Resume Next
Cells(i, myCol).FormatConditions(1).Interior.ColorIndex = 7
Next i

' Change columns on either side back to normal state
For i = 0 To 13
On Error Resume Next
Cells(1, myCol).Offset(i, 1).FormatConditions(1).Interior.ColorIndex = 15
Cells(1, myCol).Offset(i, -1).FormatConditions(1).Interior.ColorIndex = 15
Next i

'Change Odd row to highlighted
For j = 1 To 6
Cells(myRow, j).FormatConditions(1).Interior.ColorIndex = 7
Next j
End If

' For the even rows:
If Target.Row Mod 2 = 0 Then

For i = 0 To 13
On Error Resume Next
Cells(1, myCol).Offset(i, 1).FormatConditions(1).Interior.ColorIndex = 15
Cells(1, myCol).Offset(i, -1).FormatConditions(1).Interior.ColorIndex = 15
Next i

For j = 0 To 6
On Error Resume Next
Cells(myRow, 1).Offset(1, j).FormatConditions(1).Interior.ColorIndex = 15
Cells(myRow, 1).Offset(-1, j).FormatConditions(1).Interior.ColorIndex = 15
Next j

For i = 1 To 13
On Error Resume Next
Cells(i, myCol).FormatConditions(1).Interior.ColorIndex = 7
Next i

End If

Application.ScreenUpdating = True
End Sub