Apply conditional formatting to selected cells using VBA

Dear Experts:

I would like to apply conditional formatting to selected cells applying the following conditional formatting:

Interior.Color = RGB (243, 17,19)
Font.Color = RGB (255, 255, 255)

The conditional formatting is based on a formula:

=COUNTIF(A$2:A2,A2)=1 'example

The selection is always a vertical selection of cells and in the above example the selected cells start at A2. But of course the selection could also start a B5 and extend to B400 or C2 and extend to C1200

Is there a way to run a macro to apply the above conditional formatting to any vertical selection I wish to make.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
Andreas HermleTeam leaderAsked:
Who is Participating?
Rgonzo1971Connect With a Mentor Commented:

pls try
Sub Macro1()
    Set selRng = Selection.Resize(, 1) 'restrict to 1st column
    rng1 = selRng.Resize(1, 1).Address(1, 0)
    rng2 = selRng.Resize(1, 1).Address(0, 0)
    ' maybe change ","  to ";" and SUMMEWENN for your localization
    selRng.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=COUNTIF(" & rng1 & ":" & rng2 & "," & rng2 & ")=1"
    With selRng.FormatConditions(1)
        .Font.Color = RGB(255, 255, 255)
        .Interior.Color = RGB(243, 17, 19)
    End With
End Sub

Open in new window

Ryan ChongCommented:
can you provide a sample with expected output here? it would be great for illustration.
Andreas HermleTeam leaderAuthor Commented:
Hi Ryan,

here we are, thank you very much.

Regards, Andreas

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Ryan ChongCommented:
sorry as don't have time at this moment to propose a proper solution.

but you can try refer to this and see if it helps

Override Conditional Formatting with Worksheet_SelectionChange

which need to be customized.
Andreas HermleTeam leaderAuthor Commented:
ok, I will give this a try, thank you very much for it.
Andreas HermleTeam leaderAuthor Commented:
Wow, Rafael, I am really deeply impressed by your expertise. Works like a charm. I had to tweak it as you suggested, i.e. replace the english formula terms with the german formula arguments.

Really, a very nice job from your side, as always.

I really highly appreciate your expertise. Thank you very much.

Best Regards, Andreas
Ryan ChongCommented:
Rgonzo1971's solution will work but it will also remove the existing Conditional Formatting for selected cell, if that's fine for you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.