Copy only certain formats

JeffD

New Member
Joined
Feb 5, 2009
Messages
35
Hi, I am trying to make a macro or even an if statement (preferably a macro) which will only grab certain criteria from a cell. Here is my situation:

If cell A1 is yellow, then copy just the yellow format and make cells A2 - A20 yellow as well. The problem I have is the macro i have just copies the cell formats which changes some of the cells from A2 - A20 from a customized number format to a general number format. Is there any way to do this?

Thanks in advance.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
You can avoid VBA if you conditionally format cell A1 to be yellow based on whatever criteria you are currently employing, and then CF A2:A20 to be yellow if cell A1 meets that condition.

Otherwise, post back to confirm how the yellow shade gets into cell A1 in the first place, is it by manual format from the palette or did you conditionally format that cell already.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
You could do this:

Code:
Sub CopyColor()
Range("A2:A20").Interior.ColorIndex = Range("A1").Interior.ColorIndex
End Sub
 

JeffD

New Member
Joined
Feb 5, 2009
Messages
35
You can avoid VBA if you conditionally format cell A1 to be yellow based on whatever criteria you are currently employing, and then CF A2:A20 to be yellow if cell A1 meets that condition.

Otherwise, post back to confirm how the yellow shade gets into cell A1 in the first place, is it by manual format from the palette or did you conditionally format that cell already.
Condiational format would be ok, the cell gets it's colour manually to begin with from the pallette. I am not an expert with this stuff, can you give me an example of how this would look with conditional format please.
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224

ADVERTISEMENT

Can you first give an example of:
- the kinds of values that would be in cell A1
- the kinds of values that would make cell A1 be shaded yellow
- why it is (the criteria) that the values possess which would make cell A1 be yellow.

Also clarify:
- if cell A1 contains a formula or is manually entered.
- what version of Excel you are using.
 

JeffD

New Member
Joined
Feb 5, 2009
Messages
35
<TABLE style="WIDTH: 1191pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1589 border=0 x:str><COLGROUP><COL style="WIDTH: 184pt; mso-width-source: userset; mso-width-alt: 8960" width=245><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5376" width=147><COL style="WIDTH: 112pt; mso-width-source: userset; mso-width-alt: 5449" width=149><COL style="WIDTH: 116pt; mso-width-source: userset; mso-width-alt: 5668" width=155><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" span=2 width=102><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" span=2 width=103><TBODY><TR style="HEIGHT: 18pt" height=24><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BACKGROUND: #ffcc99; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 18pt; mso-ignore: style; mso-pattern: auto none" width=245 height=24>PLANT 2</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=79>2ND</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=79 x:num="0.33333333333333331">08:00</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; WIDTH: 77pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=103 x:num="0.3819433333333333" x:fmla='=IF(C1<>"",C1+0.04861,"")'>09:10</TD><TD class=xl36 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; WIDTH: 110pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=147>SS5</TD><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; WIDTH: 112pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=149 x:num="39903.482569444444" x:fmla="=G1-(160/24/60)">03/31/2009 11:34: </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; WIDTH: 116pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=155 x:num="39903.593680555554">03/31/2009 14:14: </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; WIDTH: 77pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ffcc99" width=102>0312-0060</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; WIDTH: 77pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ffcc99" width=102>2-112-1</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; WIDTH: 77pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ffcc99" width=103>9SLH-AD8</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; WIDTH: 89pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ffcc99" width=119>A410</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; WIDTH: 77pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ffcc99" width=103>B</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; WIDTH: 77pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ffcc99" width=103 x:num>30</TD></TR><TR style="HEIGHT: 18pt" height=24><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BACKGROUND: #ffcc99; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 18pt; mso-ignore: style; mso-pattern: auto none" height=24>PLANT 2</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent"> </TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent"> </TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:str="" x:fmla='=IF(C2<>"",C2+0.04861,"")'> </TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent"> </TD><TD class=xl35 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num="39903.505486111113" x:fmla="=G2-(160/24/60)">03/31/2009 12:07: </TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num="39903.616597222222">03/31/2009 14:47: </TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99">0312-0070</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99">2-113-1</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99">9SLH-AD6</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99">A210</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99">B</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc99" x:num>30</TD></TR></TBODY></TABLE>
Here is what the sheet looks like. It will be several cells going down all in column A. I double checked this and cell A1 is a conditional format which says if the text states "PLANT 2" then fill beige color. All the cells in the example that are not beige, I need to make beige. Technically, all of the cells across could be made beige based on the "PLANT 2" cell. The ones which are beige on the right side of example are entered manually.
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
You did not answer my question about which Excel version you are using.

If this data starts in row 1, then

In Excel version 2003 or before:
Select the range from cell B1 to Mx (where "x" is the last row of data in your table)
From the worksheet menu, click Format > Conditional Formatting.
In the CF dialog, click the down arrow and select Formula Is.
In the formula field, enter or paste in
=ISNUMBER(SEARCH("PLANT 2",$A1))
Click the Format button.
Click the Patterns tab.
Select your color from the palette.
Click OK and then click OK.


If you are using 2007:
Select the range from cell B1 to Mx (where "x" is the last row of data in your table)
From the Ribbon, in the Styles section, click Conditional Formatting.
From the menu, click New Rule.
In the "Select a rule type" section, click "Use a formula to determine which cells to format".
In the "Format values where this formula is true" field enter or paste in
=ISNUMBER(SEARCH("PLANT 2",$A1))
Click the Format button.
Click the Fill tab.
Select your color from the palette.
Click OK and then click OK.


Note, SEARCH is not case sensitive so if the valuein column A is plant 2 the CF will still be applied.

Also note the $A1 for absolute reference to column A.

Also note that you posted PLANT 2 so I put PLANT 2 in the formula.
That's
P - L - A - N - T - space - 2
So if you really have PLANT2 (no space) the CF will not work.

Finally, adjust the formula if your data starts on a row other than row 1.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,122
Messages
5,599,826
Members
414,341
Latest member
Mohammedsobhey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top