Conditional formatting by merged cells

Imh0tep

New Member
Joined
Feb 7, 2019
Messages
14
Hello,

I have set conditional formatting in specific range. If is not empty then background color = green + outer borders.

I sometimes use merged cells in this range and by merged cells is the borders unfortunately not complet.

Is there any way how to solve it?

thank you very much

Miroslav

 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
8,045
Office Version
2019
Platform
Windows
Merging, unmerging, inserting or deleting rows or columns will break the range that the format is applied to.

Anything merged before you apply the formatting will be formatted correctly, anything merged after will not.

Resetting the 'Applies to' range after merging should fix it.
 

Imh0tep

New Member
Joined
Feb 7, 2019
Messages
14
It's a pity because the users will create merged cells dynamically (after setting of the conditinal formatting). It's strange that the formatting of background color works by merged cells fine but the outer borders not.

Anyway thank you for your answer
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
8,045
Office Version
2019
Platform
Windows
Techincally, the borders are working correctly and the background colour is wrong.

If you are able to use a vba fix then it should be possible to use that to reset the format range automatically when the cells are merged.
Is this a method that you would be able to use?
 

Imh0tep

New Member
Joined
Feb 7, 2019
Messages
14
Unfortunately I have no idea how to fix it with the help of VBA. Could I ask you to help me with the code? Maybe the code will be not so long/complicated.

I really appreciate your help

Thank you
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
8,045
Office Version
2019
Platform
Windows
See if this works.

Go to excel, right click the sheet tab (the name of the sheet at the bottom) for the sheet where you want the conditional formatting to be applied, then click 'View code'

Copy the code and paste it into the window that opened when you clicked 'View code' in excel.

Change the range shown in red to match the range where the conditional formatting should be applied.

Close the editor and save your workbook as 'macro enabled'
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Range("[COLOR="#FF0000"]A2:I20[/COLOR]")
If Not Intersect(Target, rng) Is Nothing Then
    With rng.FormatConditions
        .Delete
        .Add xlNoBlanksCondition
    End With
    With rng.FormatConditions(1)
        .Borders.LineStyle = xlContinuous
        With .Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent3
            .TintAndShade = 0.399975585192419
            .PatternTintAndShade = 0
        End With
    End With
End If
End Sub
Note that this works if you merge the cells then enter the name, but not if you enter the name first.
 

Imh0tep

New Member
Joined
Feb 7, 2019
Messages
14
It work's great. Thank you very much. I understand the code except the target. Where have you defined, what the target is? It's variable isn't it? I think logically that the target is a cell which has been changed, but where it's strictly defined. It's rule of VBA? I would like to know more about target.

anyway thanks a lot for your time
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
8,045
Office Version
2019
Platform
Windows
The best way that I can describe Target is a varaible that is already defined as part of excel.

I don't fully understand why it works, I just know how to use it.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,137
Office Version
365
Platform
Windows
"Target" is relevant with Event Procedures, which are event that automatically trigger and run VBA code.
You are using a "Worksheet_Change" event procedure, which is VBA code tha tis automatically triggered whenever a range is manually updated in Excel.
Note the first line of the code:
Code:
Private Sub Worksheet_Change(ByVal [COLOR=#ff0000]Target[/COLOR] As Range)
So, "Target" is a dynamic range variable. Basically, it is the range that is being updated that is triggering the code to run.
So, if you are updating cell B10, Target is the range B10.
If you do a copy and paste onto a multi-cell range, Target will be that multi-cell range.

Does that clarify things?
 

Imh0tep

New Member
Joined
Feb 7, 2019
Messages
14
yes, thank you for the explanation. Could I iask one more question to the code from jason75?
Code:
If Not Intersect(Target, rng) Is Nothing Then
    With rng.FormatConditions
        .Delete
        .Add xlNoBlanksCondition
    End With
    With rng.FormatConditions(1)
Number one (1) in the end of the last row has the same meaning as "else"? So it's used for branching isn't it? I have never seen that.

thanks a lot
 

Watch MrExcel Video

Forum statistics

Threads
1,089,760
Messages
5,410,270
Members
403,305
Latest member
tray2014

This Week's Hot Topics

Top