Unconditional Formatting variable

[stephicohu]

Okay, what I want to do is I have make 29 rows with using the same colors on my conditional formatting. That means a lot of coding needs to be done! What I thought is I don't want to repeat those same code (in bold) for 29 times. I thought this would be great to have this as a function or subroutine. Each row will have unique format condition variables because they are individual values with different types and different operators specified in the add function of the format condition. Here is what I have right now:

[
'define the range and conditions

Dim RngRow As Range
Dim OtherWalkCon1 As FormatCondition
Dim OtherWalkCon2 As FormatCondition

' delete all format conditions

Set RngRow = Range("B3 : F29")
RngRow.FormatConditions.Delete

'Other Walking Count 3 >=

Set RngRow = Range("B6 : F6")

Set OtherWalkCon1 = RngRow.FormatConditions.Add(xlCellValue, xlGreater, "=3")
Set OtherWalkCon2 = RngRow.FormatConditions.Add(xlCellValue, xlLess, "=3")

'define the color with bold for green condition

[B]OtherWalkCon1.Font.Bold = True

With OtherWalkCon1.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
End With[/B]

'define the color with bold for red condition

OtherWalkCon2.Font.Bold = True

[B]With OtherWalkCon2.Font
        .Color = -16776961
        .TintAndShade = 0
End With
With OtherWalkCon2.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 11841535
        .TintAndShade = 0
        .PatternTintAndShade = 0
End With[/B]



End Sub

]

The question I have is how do I make a generic variable for the formatconditions so I can make either a function or subroutine for the code that is bold? That way I don't have to copy and paste these lines for 29 times!


Thanks...

Stephanie

 

[James006]

Hi,

If my understanding is correct, among your 29 rows there are several potential cases ...

Conditional Formatting is precisely capable of handling the determining factors and produce your expected result.

1. Could you list all the elements and conditions you are considering ?
2. Formulas can, then, be structured to exactly reflect your constraints

 

[stephicohu]

James,

I think I understand what you are talking about. In this report, the elements I am using is >=, > , <=, <. The conditions are arrange so I have 8 groups that the same value lump together on separate rows. The rest of the rows are different values. The same interior and text colors are to be used for conditions used.

Is this what you are asking about? I am a new a VBA coding. I forgot to mentioned that I work on a Mac and using Office 365.

Thanks

Stephanie....

 

[James006]

Hi again,

You should not worry about VBA in the first place ... a macro is nothing but automating a given logical pattern ...

So the crucial part is to explicit in plain English your 8 distinct cases ...