Solved

How do you write VBA Code for mutiple Conditional Formats

Posted on 2011-03-24
10
672 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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.
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 …
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.

634 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