[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How do you write VBA Code for mutiple Conditional Formats

Posted on 2011-03-24
10
Medium Priority
?
675 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 2000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

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.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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 in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

829 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