Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Conditional Formatting Fails
Hi,
When I attempt to copy the conditional formatting of the following, it does not copy correctly. a b c 1 First MI Last 2 Jack M Harris 3 Sally J Smith 4 Bill V Nelson Cells A2 - C2 have conditional formatting applied to them. I have attempted to Copy - Paste Special -- Formats, but this code fails: Range("A1:C1").Select Selection.Copy Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range("A2:C4").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False I will be calculating the row count via code (I don't have that handy) so the absolute reference to C4 will be replaced with C & rowcount. Any help you can lend would be appreciated. Henry |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Conditional Formatting Fails
Hi Henry,
I could be wrong but your code is copying A1:C1 and not A2:C2, which has the conditional formatting applied to them. Try amending the first line of code to copy from row 2! Good luck, Les Bantleman Henry Stockbridge wrote: Hi, When I attempt to copy the conditional formatting of the following, it does not copy correctly. a b c 1 First MI Last 2 Jack M Harris 3 Sally J Smith 4 Bill V Nelson Cells A2 - C2 have conditional formatting applied to them. I have attempted to Copy - Paste Special -- Formats, but this code fails: Range("A1:C1").Select Selection.Copy Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range("A2:C4").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False I will be calculating the row count via code (I don't have that handy) so the absolute reference to C4 will be replaced with C & rowcount. Any help you can lend would be appreciated. Henry |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Conditional Formatting Fails
Thats because when you use conditional formatting your expression looks something like this =$A$1=""fred", notice the $ it makes things an absolute reference if your expression looked like this =$A1=""fred" it will copy down fine! Regards, Simon -- Simon Lloyd |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Conditional Formatting Fails
Simon Lloyd wrote: Thats because when you use conditional formatting your expression looks something like this =$A$1=""fred", notice the $ it makes things an absolute reference if your expression looked like this =$A1=""fred" it will copy down fine! Regards, Simon -- Simon Lloyd ------------------------------------------------------ Thanks for the help. Henry |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
loopk up in Excel
Simon Lloyd;7036717 Wrote: This may give you something to work with! Code: -------------------- =IF(COUNTIF(Sheet1!$A:$A,VLOOKUP("Fred",Sheet1!$A: $A,1,FALSE)),Sheet1!A1,"") -------------------- This formula can be copied across and then down and will show the users data, its not perfect by any means as i am a beginner at this but it may prompt someone to perfect it for you! Try copying it in to sheet 2 A1 and copy across and down it will fill in any instance of Fred and related data but only in the rows he appears! Regards, SImonForgot to mention that you could then filter the sheet for Non-Blanks to see all of your data! Regards, Simon -- Simon Lloyd |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
loopk up in Excel
A friend of mine at another forum, Bob Phillips had a look at your rpoblem and then sent me this link which has a worked example on it and will achieve what you need! http://cjoint.com/?mocvNF4mEp Regards, Simon -- Simon Lloyd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying conditional formatting | Excel Discussion (Misc queries) | |||
Copying Conditional formatting | Excel Discussion (Misc queries) | |||
Conditional Formatting: Logical AND fails to work with INDIRECT(.. | Excel Worksheet Functions | |||
copying conditional formatting | Excel Discussion (Misc queries) | |||
Copying Conditional Formatting | Excel Discussion (Misc queries) |