CODEWITHSUNDEEP
excelvba
Franky
Franky

Reputation: 21

Detecting Conditional formatting within a range

I've been trying to set up a spreadsheet on excel that works a lot of calculations out but is also allowing for variables within it (i.e the team numbers change sometimes).

In this case I have 4 separate teams. Each team has it's own default conditional formatting which will set a default colour for that team. I've done this by just detecting if the cell is blank.

Example of teams

As you can see the default colours are blue and orange for shown 2 teams.

The issue I have is when someone copies & pastes from Orange to Blue team it takes on the formatting of said team. For example if I copy and paste from Orange team where it says "COURSE" in yellow into the blue team, when I delete the word "COURSE" it should default back to blue but as it has been copied from Orange it defaults back to orange.

I have managed to set the following which resets the formatting back to its original blue default (This is currently done from a button press for debug purposes).

Range(MyRange1).Select


Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=LEN(TRIM(D2))=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorLight2
    .TintAndShade = 0.599963377788629
End With
Selection.FormatConditions(1).StopIfTrue = False

My issue is eventually I want to link this to a worksheet change to allow for copy & pasting. But every time it runs it creates a duplicate version of the conditional formatting within the rules.

So I need to delete any other instances of the condition that previously exists, otherwise overtime they will stack up.

I know it's going to be an IF/THEN statement but for the life of me I cannot think how to test for it.

I have severe writer's block at the moment so I really hope this makes sense!

Upvotes: 1

Views: 51

Answers (2)

CLR
CLR

Reputation: 12289

This will cycle through all the conditional formats applied in MyRange1 and delete any that are not priority one.

For Each f In MyRange1.FormatConditions
    If f.Priority <> 1 Then f.Delete
Next

Sorry, just noticed that you use MyRange1 as the range address, so you'd use:

For Each f In Range(MyRange1).FormatConditions
    If f.Priority <> 1 Then f.Delete
Next

Upvotes: 1

Chris
Chris

Reputation: 181

Could you just delete the old conditions and then reinstate them every time you copy and paste?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)

Range("A1:A100").Select

With Selection.FormatConditions.Delete
End With

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=LEN(TRIM(D2))=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorLight2
    .TintAndShade = 0.599963377788629
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

Upvotes: 0

Related Questions