• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 677
  • Last Modified:

How do you write VBA Code for mutiple Conditional Formats

I am having difficulty with the attached code all the formats are not being correctly added.

Please help
CurConst.Range(CurConst.Cells(5, 1), CurConst.Cells(CurConstR1, CurConstC1)).Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$C5=""Need To Review"""
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$C5=""New Tab"""
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$C5=""Added"""
        With Selection.FormatConditions(1)
            .Font.Bold = True
            .Interior.Color = 8781823
            .StopIfTrue = True
        End With
        With Selection.FormatConditions(2)
            .Font.Bold = True
            .Interior.Color = 14024661
            .StopIfTrue = True
        End With
        With Selection.FormatConditions(3)
            .Font.Bold = True
            .Interior.Color = 49407
            .StopIfTrue = True
        End With

Open in new window

0
bearblack
Asked:
bearblack
  • 5
  • 5
1 Solution
 
rspahitzCommented:
Code looks fine, and you should have 3 conditional formats for cell A5, showing a different color depending on the value of cell C5
Make sure C5 doesn't have any leading or trailing spaces.

What did you want the code to do, and why do you think it's not working?

(note that you only put a code snippet there so it's difficult to test it...)
0
 
bearblackAuthor Commented:
The font on the 2 condition does not go bold
0
 
bearblackAuthor Commented:
I want the entire line of data to conditional format based on the values in column C.
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
bearblackAuthor Commented:
CurConst.Range(CurConst.Cells(5, 1), CurConst.Cells(CurConstR1, CurConstC1)).Select

CurConst = Worksheet Current Construction Tab


CurConstR1 is the last row of data
CurConstC1 is the laste column of data
0
 
rspahitzCommented:
For starters,

Instead of this:

CurConst.Range(CurConst.Cells(5, 1), CurConst.Cells(CurConstR1, CurConstC1)).Select

Try this:

(assuming that the sheet is already active)
    Rows(5).Select


Other than that, it turns bold for me.  Maybe the font or size you are using doesn't appear bold; try a different font or size to check.

0
 
rspahitzCommented:
Sorry...you wanted multiple rows...replace the line I gave you above with this:


    Dim strLastColumn As String
    strLastColumn = Split(Cells(CurConstC1).Address, "$")(1)
   
    Range("A5:" & strLastColumn & CurConstR1).Select
0
 
rspahitzCommented:
If the entire row can be colored (rather than only to a specific column, it's a bit easier since you don't need to get the column letter:

 Range("5:" & CurConstR1).Select
0
 
bearblackAuthor Commented:
Still one of the three does not bold usually the second condition. After running the macro I can go in to the conditional formatting icon and see that it is not formated as bold. I would like the entire row formatted but not beyond the last data column. If I topggle the bold in the second then the third condition does not bold.

Getting really frustrated.
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""New Tab"""
        With Selection.FormatConditions(2)
            .Font.Bold = False
            .Font.Bold = True
            .Interior.Color = 14024661
            .StopIfTrue = True
        End With
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""BR-Added"""
       With Selection.FormatConditions(3)
            .Font.Bold = True
            .Interior.Color = 49407
            .StopIfTrue = True
        End With

Open in new window

0
 
bearblackAuthor Commented:
Just for additional clarification the interior color works correctly in all conditions
0
 
rspahitzCommented:
Well, I'm not sure why you're having bolding problems. It all seems to work for me.
You can also try to remove any existing bold from the cells; maybe that's causing it to toggle, although it shouldn't.

anyway, I guess one of the solutions was close enough for now.  Good luck moving forward.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now