Solved

How do you write VBA Code for mutiple Conditional Formats

Posted on 2011-03-24
10
666 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

840 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