?
Solved

Conditional Formatting not copying properly.

Posted on 2014-01-17
12
Medium Priority
?
584 Views
Last Modified: 2014-01-17
I have a conditional format on a cell that I would like to copy down the whole sheet.
    Range("G3").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(G3=""PASSED"",TRUE,FALSE)"
    Range("G3").FormatConditions(Range("G3").FormatConditions.Count).SetFirstPriority
    Range("G3").FormatConditions(1).Font.Color = -11489280
    Range("G3").FormatConditions(1).Font.TintAndShade = 0
    Range("G3").FormatConditions(1).StopIfTrue = False

Open in new window


The problem is when I copy it down using the format painter, all the cells will reference the first cell in the range I'm formatting so in this case they are all looking at G4 instead of their respective rows.
I could do this with a loop but I'd rather not if possible. It takes too long to run.
Any idea?
0
Comment
Question by:stopher2475
[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
12 Comments
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39788642
Since your formula is just checking for equality to a specific value you can use cell value equals instead of an expression:
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""PASSED"""

Open in new window


(I think your formula isn't working because the G3 is hardcoded into the formula and apparently the format painter isn't changing it on paste - this dodges that issue)
0
 
LVL 46

Expert Comment

by:aikimark
ID: 39788734
Try using r1c1 (relative addressing) in your .Add method.
Formula1:="=IF(RC=""PASSED"",TRUE,FALSE)"

Open in new window

0
 
LVL 2

Author Comment

by:stopher2475
ID: 39788835
I actually want to leave it as G3 becuase I'm going to format the 2 cells to the left of it based on that cells value. Will try the RC notation. I think aikimark is onto something.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39788850
Attacking the problem from the other direction, what code are you using for the format painter?

I just recorded a macro and it gave me this (and seemed to work):
    Selection.Copy
    Range("C7:C8").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

Open in new window


Also, if you're going to use the same formula on another column you probably want a $ infront of your G to make it $G3.
0
 
LVL 2

Author Comment

by:stopher2475
ID: 39788861
Tried the RC notation for the range.
Range("G3:G2310").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(RC=""PASSED"",TRUE,FALSE)"
    Range("G3:G2310").FormatConditions(Range("G3:G2310").FormatConditions.Count).SetFirstPriority
    Range("G3:G2310").FormatConditions(1).Font.Color = -11489280
    Range("G3:G2310").FormatConditions(1).Font.TintAndShade = 0
    Range("G3:G2310").FormatConditions(1).StopIfTrue = False]

Open in new window

The expression in all of them is referencing the first cell of the range.
    =IF(G3="PASSED",TRUE,FALSE)
How can I get it to reference the rows validation cell?
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39788964
Try:
   
    Range("G3").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(G3=""PASSED"",TRUE,FALSE)"
    Range("G3").FormatConditions(Range("G3").FormatConditions.Count).SetFirstPriority
    Range("G3").FormatConditions(1).Font.Color = -11489280
    Range("G3").FormatConditions(1).Font.TintAndShade = 0
    Range("G3").FormatConditions(1).StopIfTrue = False
    Range("G3").Copy
    Range("G4:G20").Select
    Range("G4").PasteSpecial xlPasteAllMergingConditionalFormats

Open in new window

0
 
LVL 2

Author Comment

by:stopher2475
ID: 39789002
That only does the one cell G4. I change line 8 to:
Range("G4:G20").PasteSpecial xlPasteAllMergingConditionalFormats
but they all reference G4 for the check.
I think I may be stuck with using a loop. Sucks because it's a lot slower.
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39789157
stopher, I think you missed my last comment.

    Selection.Copy
    Range("C7:C8").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

Open in new window


Produced this paste formats code with record macro and this worked for me on a small scale test.
0
 
LVL 11

Accepted Solution

by:
Angelp1ay earned 2000 total points
ID: 39789184
See attached example workbook. Run the macro "demo". Works perfectly for me.

Sub Demo()

    Dim template As Range, destination As Range
    Set template = Range("G3")
    Set destination = Range("G4:G20")
    
    template.FormatConditions.Add Type:=xlExpression, Formula1:="=IF($G3=""PASSED"",TRUE,FALSE)"
    template.FormatConditions(template.FormatConditions.Count).SetFirstPriority
    With template.FormatConditions(1)
        .Font.Color = -11489280
        .Font.TintAndShade = 0
        .StopIfTrue = False
    End With

    template.Copy
    destination.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End Sub

Open in new window


Note - the conditional formatting appears as 2 rules; one covering G3 and one covering G4:G20. If you want to be fancier you should probably make the template an arbitrary cell outside the destination range, paste to the whole of G3:G20, and then wipe the template.
ConditionalFormatPaste.xlsm
0
 
LVL 2

Author Comment

by:stopher2475
ID: 39789379
What's weird is that works but when I check the format it appears to be referencing the first Cell "G4". It was probably working before but when I saw the reference to the first cell I had assumed it wasn't. I guess I don't understand the notation fully.
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39789400
In the conditional formatting manager the formula shown is always that for the first cell in the range it's applied to. We probably should have realised that was what you were looking at :/
0
 
LVL 2

Author Comment

by:stopher2475
ID: 39790193
Good to know. Thanks for the help!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

777 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