Solved: Conditional Format, Excel 2007 Help

Discussion in 'Business Applications' started by irvinbang, Mar 26, 2013.

Not open for further replies.

Joined:
Jan 25, 2013
Messages:
85
This file is from Excel 2007
This is about Conditional Formatting Help
Test file is attached

My question is how can i make row three work for the rest of the rows?

A3:A10000 is a number from 1-5
B:M is just ramdom information that i will be Pasting in

Explination:

A3 has a number from 1-5. Cell has CF so that if 1-5 is entered that number will be a certain color.
Once number is in A3 the other cells B3:M3 will change that same color.

Now if i put any of the numbers 1-5 the cells B3:M3 will change what ever color number is in A3.

With that being said if i take and highlight B3:M3 and take the little small box down at the bottom right hand of the last cell, click and drag down to duplicate the CF, all the cells now refer to A3.

I would like for Each row from A3:A10000 to be able to put the number 1-5 and all the cells with that row B:M to change to the same color.

I have row 3 set up like i want. For example just change the number in A3 from 1 through 5 and you will see what im talking about.

Now i can go in and change the CF for each row but i dont have that kinda time to go from A3:A10000

If anyone can help, Thanks in advance.

File size:
13.7 KB
Views:
30
2. etafModerator

Joined:
Oct 2, 2003
Messages:
65,294
First Name:
Wayne
remove the \$ sign from row 3 D-M
and also the range it applies to - remove the \$

then copy D3-M3 and then select
D3-M10000 and paste special - format

the \$ is making it stay on A3 - by removing the \$ when you copy it moves with the row

Not sure , but looking in the row 3 - you have some formulas that refer to A2-A22 ???

so in A3 it changes colour
then in B3-M3 you want the formula to test for the value in A3 only - in fact use A\$3 then you copy from B to M
no \$s
then copy - paste special the format into all the other cells

I think i have done it for you here - see attached

Attached Files:

• Defi test_etaf.zip
File size:
122.2 KB
Views:
15

Joined:
Jan 25, 2013
Messages:
85
Maybe i should have been using the copy and paste rather than clicking the bottom right box to drag down.

As far as the \$ being in front i cant remove them. As soon as i delete them and click ok or apply excel puts them right back in there.

Now i have to take an put in the accual information and see if it still works with the copy and paste.

Well let you know by tomorrow.

4. Garf13LD

Joined:
Apr 17, 2012
Messages:
455
If you have other plans for Row 1 and 2, append
*(row()>2)
to all CFs.

If you need to insert row, do an insert and then fill the previous row down.

Copy and paste more than one column at the same time might "duplicate" the CFs, if that is the case do copy and paste column by column.

Copying and pasting up and down a single column will not affect the CF.

If there is a need to replace mass amount of data, highlight a (whole) row that is not affected by the change and do a format paint.
Make sure when you "paste" paint, you include the original row that you copy.

Attached Files:

• Defi test.xlsx
File size:
14.2 KB
Views:
32

Joined:
Jan 25, 2013
Messages:
85
Worked good. sorry it took me so long to get it working. Have been very busy at work.

As Seen On