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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
8,004
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,004
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,004
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,004
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,094
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
 

Forum statistics

Threads
1,089,490
Messages
5,408,575
Members
403,216
Latest member
Boba Fetts

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top