0 votes
in VBA by Beginner (19 points)

Dear VBA Lovers,
I want to highlight the entire row and column in Excel. I did it through Conditional Formatting and Excel VBA. What I did giving below:

In conditional Formatting, I wrote this formula for Row
=OR(CELL("row")=ROW())
and then select a color to highlight the entire row

I wrote this formula for Column
=OR(CELL("col")=COLUMN())
and then select a color to highlight the entire column

After that, I wrote this code in Excel VBA

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Application.CutCopyMode = False Then
Application.Calculate
End If
End Sub

It is working. Now I want to change the color of the intersecting cell. How can I do this?
Please help
Thanks & Regards

Alok Ghosh

1 Answer

+3 votes
by Super Expert (2.2k points)
selected by
 
Best answer

You'll need to add a 3rd conditional format to check the condition where the cell column equals the current column AND the cell row equals the current row. Here's the conditional formatting formula you'll need to apply to the whole sheet:

=AND(CELL("col")=COLUMN(),CELL("row")=ROW())

This formula needs to be the very first formula checked in your conditional formatting rules manager. Otherwise, it will get overwritten. Here's a screenshot explaining what I mean:

Notice I got rid of the OR() statement in your other conditional formatting rules. The OR statement isn't needed because you're only checking one condition.

When you're done, anytime you select a new cell, it's automatically highlighted. 

(Note: If this isn't working for you, you'll need to make sure Application.EnableEvents=True.

by Beginner (19 points)
edited by

Respected Sir,

Hats off to you. It's working...

Now two problem is arising.

1. If I write anything it is breaking the highlighted part.

2. I add this file to Add-Ins so that whenever I open Excel It should come along with highlighted, but it is not highlighting to any other file. I don't know why? but my other Add-Ins are working...

Screenshot clears the first problem...

Thanks & Regards,

Alok Ghosh

by Super Expert (2.2k points)
+1

Conditional Format Rendering

The issue you showed in your screenshot is an Excel performance issue and not a VBA issue. I've recreated that behavior when I enter values too fast for Excel to properly render. You can tell it's an Excel performance issue because if you close and reopen your workbook, it's resolved. I've found that replacing my SelectionChange event macro with the following macro, I can eliminate this misrendering artifact:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Application
    .ScreenUpdating = False
        If .CutCopyMode = False Then
            .Calculate
        End If
    .ScreenUpdating = True
End With
End Sub

Class Module for Add-in Global Event Handling

If you're packaging this as an add-in, you'll need the add-in to programatically add the conditional formatting rules to each sheet in your ActiveWorkbook. The add-in isn't highlighting rules now because you likely added the conditional formatting rules to ThisWorkbook (the add-in) instead of each subsequent workbooks you open. 

Creating the class module

This gets complicated because you'll need to create a custom Class Module in your VBA Editor (Insert > Class Module). In this Class Module, you'll create a new event trigger and monitor for the SheetActivate event. Once a sheet is activated, you'll need a macro to add your conditional formatting to the new sheet. The Class Module macro will wind up looking something like this:

Option Explicit
 
Private WithEvents oApp As Excel.Application
 
Property Set XL(Application As Excel.Application)
Set oApp = Application
End Property
Property Get XL() As Excel.Application
    Set XL = oApp
End Property
 
Private Sub oApp_SheetActivate(ByVal Sh As Object)
Dim i As Integer
Dim iCount As Integer
    With Sh.Range("1:1048576")
        'first make sure the rules are applied to the correct range if the
        ' conditional formatting already exists
        For i = 1 To .FormatConditions.Count
            If .FormatConditions(i).Formula1 = "=AND(CELL(""col"")=COLUMN(),CELL(""row"")=ROW())" Or _
                                         .FormatConditions(i).Formula1 = "=CELL(""row"")=ROW()" Or _
                                         .FormatConditions(i).Formula1 = "=CELL(""col"")=COLUMN()" Then
                iCount = iCount + 1
                .FormatConditions(i).ModifyAppliesToRange Range(.Address)
            End If
        Next i
        If iCount >= 3 Then Exit Sub 'because all 3 rules already exist
        'otherwise, you need to add the conditional formatting rules
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=AND(CELL(""col"")=COLUMN(),CELL(""row"")=ROW())"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            .StopIfTrue = False
            With .Interior
                .PatternColorIndex = xlAutomatic
                .Color = 14348258
                .TintAndShade = 0
            End With
        End With

        .FormatConditions.Add Type:=xlExpression, Formula1:="=CELL(""col"")=COLUMN()"
        With .FormatConditions(.FormatConditions.Count)
            .StopIfTrue = False
            With .Interior
                .PatternColorIndex = xlAutomatic
                .Color = 14083324
                .TintAndShade = 0
            End With
        End With
        .FormatConditions.Add Type:=xlExpression, Formula1:="=CELL(""row"")=ROW()"
        With .FormatConditions(.FormatConditions.Count)
            .StopIfTrue = False
            With .Interior
                .PatternColorIndex = xlAutomatic
                .Color = 14083324
                .TintAndShade = 0
            End With
        End With
    End With
End Sub

Private Sub oApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
With Application
    .ScreenUpdating = False
        If .CutCopyMode = False Then
            .Calculate
        End If
    .ScreenUpdating = True
End With
End Sub

Notice I went ahead and added the SheetSelectionChange event trigger to the class routine, as well. This monitors for selection changes on all open workbooks, instead of just on one sheet of your add-in file.

Creating an Instance of your Class

Once you create this class, you'll need to activate an instance of the class when you open your add-in. You can do this when your add-in opens by adding a regular module (Insert > Module) and pasting the following code:

Global EventTriggers As Class1

Sub CreateInstance()
Set EventTriggers.XL = Excel.Application
End Sub

The Class1 name needs to be changed to whatever you named your Class Module.

After you do this, you'll need to add this macro to the ThisWorkbook module of your add-in project:

Private Sub Workbook_Open()
    Module1.CreateInstance
End Sub

The Module1 name needs to be changed to whatever you named your regular Module. You can run this module manually the first time you paste it to activate it, but it will run automatically each time you open your .xlam file.

How it works

When you have the class in your add-in set up correctly, your add-in will monitor for any selection change or sheet activation events in any other open workbook. This is the power of class-level event handling. Your selected cell will be marked each time someone clicks a new cell, regardless of which workbook they have open or which sheet they're on. As long as the add-in is open, it will work.

Warning about Conditional Formatting Fragmentation

I want to caution you that you'll need to carefully handle conditional formatting fragmentation which occurs when you cut/paste cells on your sheet. The conditional formatting formulas will get messed up each time your user does this, so you'll need to find an event trigger and periodically correct the ModifyAppliesToRange property with a macro like this:

With Range("1:1048576")
    For i = 1 To .FormatConditions.Count
        If .FormatConditions(i).Formula1 = "=AND(CELL(""col"")=COLUMN(),CELL(""row"")=ROW())" Or _
                                     .FormatConditions(i).Formula1 = "=CELL(""row"")=ROW()" Or _
                                     .FormatConditions(i).Formula1 = "=CELL(""col"")=COLUMN()" Then
            .FormatConditions(i).ModifyAppliesToRange Range(.Address)
        End If
    Next i
End With

I included a sample in the SheetActivate routine to take care of this for you, but you may want to change when or how often this "refresh" occurs. 

by Beginner (19 points)
Dear Sir,
Thanks for great help. You are a VBA Expert. Code is the most advanced level and I have just started learning Excel VBA. I paste your code but not working. I know, that I am doing wrong somewhere, due to lack of knowledge. It shows that whatever I am thinking everything is possible in Excel and by Excel VBA.
I saw that-
1. If I lock any cell to hide the formula, code is giving an error.
2. During printing highlighted row and column is also printing, obviously, it will print.
Any code to get rid off from these two.
Thanks & Regards
Alok Ghosh

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.

Register

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...