Solved

How do you write VBA Code for mutiple Conditional Formats

Posted on 2011-03-24
10
667 Views
Last Modified: 2012-05-11
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
Comment
Question by:bearblack
  • 5
  • 5
10 Comments
 
LVL 22

Accepted Solution

by:
rspahitz earned 500 total points
ID: 35210992
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
 
LVL 2

Author Comment

by:bearblack
ID: 35211091
The font on the 2 condition does not go bold
0
 
LVL 2

Author Comment

by:bearblack
ID: 35211141
I want the entire line of data to conditional format based on the values in column C.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:bearblack
ID: 35211158
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
 
LVL 22

Expert Comment

by:rspahitz
ID: 35211227
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
 
LVL 22

Expert Comment

by:rspahitz
ID: 35211300
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
 
LVL 22

Expert Comment

by:rspahitz
ID: 35211338
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
 
LVL 2

Author Comment

by:bearblack
ID: 35211489
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
 
LVL 2

Author Comment

by:bearblack
ID: 35211523
Just for additional clarification the interior color works correctly in all conditions
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35211728
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question