Conditional Formatting

[omairhe]

hi, in conditional formatting i have a problem with the way it works
here is an example

Items for Sale............Items Left in Stock...........Minimum Value
23..................................3..................................1
23.................................54..................................1
23..................................3..................................1
533................................65.................................1
222................................56.................................1
15..................................23.................................1
435................................77.................................1
45..................................3..................................1

Now i select first row that is 23....3....1.
then i do conditioanl formatting for instance 'Data Bars'...
but i want to do the same with rest of rows. so when i drag the formatting
on the below data, then the 'Data Bars' width changes in first row (23....3....1).
i dont want the data from 1st row to interlink with the below rows but i want only the conditional formatting rule which was applied on first row limited to horizontally only.
so to put it in easy terms i want to do the same thing i did with first row
but this time with the rest of the rows without interlinking their values with other rows..pls give an answer to this problem.. thanks

 

[Jonmo1]

Instead of dragging the formatting down,

Copy row 1,
Highlight Row 2 (or all remaining rows) - Right Click - Paste Special - Formats

also, you could enter the conditional formatting in all the rows in the first place.

highlight ALL rows, click format - conditional formatting, and enter the condition as you did before.

Hope that helps..

 

[Stu Dapples]

Or in COnditional format menu, look at list of rules, click 'copy rule' and then apply to required range.... Same thing, just another way round it!

 

[leocoppens]

I presume the problem is in the absolute reference that is automatically applied to the conditional formating. If you convert the cell reference to be relative you will be able to drag (copy) your conditional format the way you want.

Best regards,
Leo

 

[omairhe]

jeez i tried it but it didnt work.. same like before...if u put 0........0.......1 in the data above as 2nd row then it is obvious to know....

so right now im about to try a software that converts absolute reference to relative n vice versa....

thank you for the help

 

[omairhe]

so this is how i got a work around with this problem....
1. Filter the first column and check 0 values.
2. select entire column, and delete formulas from the 0's , this will give you 0 = blanks
3. now start with recording a macro.
4. go to first column u want conditional formatting in.
5. hit end on the keyboard and press down key one time only.
6. select the row which is not blank (23......3.......1).
7. now do conditional formatting for bars for these 3 cells.
8. stop recording macro.
9. i assigned macro a hot key like (ctrl+i).
10. keep pressing ctrl+i till u finish up with all data.
11. now select blanks from filter and apply back the formula deleted in step 2.
now u ask y all this?.. well my actual data is huge and when conditional formatting applied to them my excel 2007 go slow so this way conditional formatting got limited for value data and not for blanks.,,

 

[omairhe]

5. hit "end" on the keyboard and press the "down arrow" key one time only.