Excel Macro to Give Specific Conditional Formatting to a Row

JohnTravolski

New Member
Joined
Nov 25, 2015
Messages
38
Office Version
  1. 2019
Platform
  1. Windows
I want to use an Excel Macro to give a specific part of a row (for example `B1:G1`) the conditional formatting that highlights any duplicate cells in the row as red. In other words, the equivalent of choosing this option with that specific row selected:

r/excel - Excel Macro to Give Specific Conditional Formatting to a Row
It's important that it only applies to cells in that range. If I run the macro again with cells `B2:G2`, the conditional formatting should not consider any values in `B1:G1` (this is the default (and desired) behavior of choosing the option in the picture).

How would I do this with an Excel macro?
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
I am not sure that I have correctly understood what you are trying to achieve, but you could try this selection-change event code with a copy of your workbook.
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.

Note that this will remove any existing Conditional Formatting in the worksheet.

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Cells.FormatConditions.Delete
  With Selection
    .FormatConditions.AddUniqueValues
    .FormatConditions(1).DupeUnique = xlDuplicate
    .FormatConditions(1).Interior.Color = vbRed
  End With
End Sub
 

JohnTravolski

New Member
Joined
Nov 25, 2015
Messages
38
Office Version
  1. 2019
Platform
  1. Windows
I am seeking the equivalent of what is done in this video, where the specified range is explicitly given in the code (rather than derived from the sheet's active selection). I do NOT want to clear any existing conditional formatting rules.
I'm using Office Professional Plus 2019.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I'm using Office Professional Plus 2019.
Thanks. Please put that in your account details as described in my previous post so that the information is always available to helpers.


I am seeking the equivalent of what is done in this video, where the specified range is explicitly given in the code (rather than derived from the sheet's active selection). I do NOT want to clear any existing conditional formatting rules.
OK, understand better now.

If I run the macro again with cells `B2:G2`, the conditional formatting should not consider any values in `B1:G1`
There should be no need to run a macro multiple times to get the formatting on multiple rows.

See if this is closer to the mark. You said for a range explicitly given in the code so I have chosen B1:G10 as my example.

VBA Code:
Sub CFDupesInRow()
    With Range("B1:G10")
      .FormatConditions.Add Type:=xlExpression, Formula1:=Replace("=COUNTIF($B#:$G#,B#)>1", "#", .Row)
      .FormatConditions(.FormatConditions.Count).SetFirstPriority
      .FormatConditions(1).Interior.Color = vbRed
    End With
End Sub

Result for my sample data:

JohnTravolski 1.xlsm
BCDEFG
1245845
2441174
3699983
4638921
5445412
6126879
7739877
8724648
9366787
10393586
Sheet1
 

JohnTravolski

New Member
Joined
Nov 25, 2015
Messages
38
Office Version
  1. 2019
Platform
  1. Windows
Thanks. Please put that in your account details as described in my previous post so that the information is always available to helpers.


OK, understand better now.

There should be no need to run a macro multiple times to get the formatting on multiple rows.

See if this is closer to the mark. You said for a range explicitly given in the code so I have chosen B1:G10 as my example.

VBA Code:
Sub CFDupesInRow()
    With Range("B1:G10")
      .FormatConditions.Add Type:=xlExpression, Formula1:=Replace("=COUNTIF($B#:$G#,B#)>1", "#", .Row)
      .FormatConditions(.FormatConditions.Count).SetFirstPriority
      .FormatConditions(1).Interior.Color = vbRed
    End With
End Sub

Result for my sample data:

JohnTravolski 1.xlsm
BCDEFG
1245845
2441174
3699983
4638921
5445412
6126879
7739877
8724648
9366787
10393586
Sheet1
Works great, thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,270
Messages
5,600,638
Members
414,398
Latest member
dhune

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top