Conditional formatting by merged cells

[Imh0tep]

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

 

[jasonb75]

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]

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]

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]

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]

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'

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]

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]

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]

"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:

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]

yes, thank you for the explanation. Could I iask one more question to the code from jason75?

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