Conditional Formatting on Duplicate Cells Based on VBA

stanco

New Member
Joined
Mar 16, 2019
Messages
48
Happy New Year to you.

I am maintaining a master file which keeps track of all the applications submitted to me. This file is being updated by copying data from an excel application form (submitted by user) and then transpose and pasted into row.

I have done conditional formatting on column H (which is the company name) to spot duplicate submission but it seems like when i transpose paste, the conditional formatting is overridden. I have done a macro to refresh and redo the conditional formatting but it is too tedious to refresh everything i update the master file.

Can this be resolved by VBA please?

Thank you.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
I have done conditional formatting on column H (which is the company name) to spot duplicate submission but it seems like when i transpose paste, the conditional formatting is overridden. I have done a macro to refresh and redo the conditional formatting but it is too tedious to refresh everything i update the master file.
Try turning on the Macro Recorder as you apply these steps.
That should give you most of the VBA code that you need to do this.

If you need any help cleaning up the VBA code your recorderd to make it more dynamic (if necessary), please post the recorded VBA code with an explanation of what changes need to be made to it.
 
Upvote 0
Try turning on the Macro Recorder as you apply these steps.
That should give you most of the VBA code that you need to do this.

If you need any help cleaning up the VBA code your recorderd to make it more dynamic (if necessary), please post the recorded VBA code with an explanation of what changes need to be made to it.

Hi Joe4, happy new year to you.

I have recorded the macro but I am hoping this can be converted to VBA so that it can be run automatically. Would you be able to assist please.

VBA Code:
Sub Refresh()
'
' Refresh Macro
'
' Keyboard Shortcut: Ctrl+r
'
    Sheets("Sheet2").Select
    Columns("D:D").Select
    Selection.FormatConditions.AddUniqueValues
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).DupeUnique = xlDuplicate
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Columns("H:H").Select
    Selection.FormatConditions.AddUniqueValues
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).DupeUnique = xlDuplicate
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub
 
Upvote 0
I have recorded the macro but I am hoping this can be converted to VBA
It already is. In Excel "Macros" and "VBA" are the same thing (what you have posted is actually VBA code).

Are you looking for "Event Procedure" VBA code? That is VBA code that runs automatically upon some "event" happening, such as the opening of a file, the selection of sheet, the selection of a cell, the manual update of some cell, the saving of a file, etc.
If that is what you are looking for, please explain exactly the conditions that should "trigger" this code to run. Please be specific!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top