How to paste Conditional Formatting to other cells but using different data cell

  • Thread starter Thread starter SalD1
  • Start date Start date
S

SalD1

I've made a spreadsheet that has a column for days of testing and
another column that calculates the actual days it took for testing.
Each row has a different day requirment of when testing should be
complete. How can I format the column of "actual days of testing" to
color the cell in red with bold type when the value of this cell is
greater than the cell of "days of testing"? I tried conditional
formating, but this only works for one row, when I tried to paste the
format to other cells it used the data source from the first row. It
would be too time consuming to individually format each cell.



------------------------------------------------


-- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum
at http://www.ExcelTip.com/
------------------------------------------------
 
SalD1 said:
I've made a spreadsheet that has a column for days of testing and
another column that calculates the actual days it took for testing.
Each row has a different day requirment of when testing should be
complete. How can I format the column of "actual days of testing" to
color the cell in red with bold type when the value of this cell is
greater than the cell of "days of testing"? I tried conditional
formating, but this only works for one row, when I tried to paste the
format to other cells it used the data source from the first row. It
would be too time consuming to individually format each cell.



------------------------------------------------


-- View and post Excel related usenet messages directly from
http://www.ExcelTip.com/forum
at
http://www.ExcelTip.com/
------------------------------------------------
Click to expand...


Did you use the format painter to paste the formats over?

Have you used absolute cell refernces in your condition?
 
If you select your range (all of it that should have the same conditional
formatting), you can write your formula for the activecell. When you hit enter,
excel will adjust the formulas accordingly. Just like it does when you drag a
formula down a column.

But like "The Red Cardinal" wrote, you have to be careful with your
relative/absolute references.
 
Sounds like you have absolute referencing in your formula. I think you
used Cell value is: and Greater than: and then you chose the cell
using the range finder in the Conditional formatting box. That is the
right idea, but you have to make an adjustment to the resulting cell
reference to make this work for you.

Do the Conditional Formatting again. This time, highlight the entire
range of values of your "actual days of testing" and leave it
highlighted. Then select Format, Conditional Formatting. Under
Condition 1, you can leave Cell value is: - then click and choose
Greater than: in the next box. Now, use the range finder to highlight
the first cell in the first column (I will assume this cell is cell
A1). What you will see in the box is the following:

=$A$1

What you need to do is change this to the following (just delete the
appropriate $ sign):

=$A1

Then click the Format button to choose the desired formatting you
want. Then click OK to dismiss each box, and you should have what you
want.

Let me know if I can help further....


MRO
 
Yes, I found that I was using Absolute reference ($) in the formula.

Thanks for the reply!
 
I saw this thread quite interesting. But please let me know how to format a range of cells , say c1 to c50 and do the conditional formatting. If i select the cells to have the conditional formating and select cells that contain specific condition, and then select the range of look for cells, i get a error you cannot use direct range of cells in conditional formating. Is there any way to overcome this. I tried removing the dollar sign as stated in the reply in this thread in the previous posts. can i copy the conditional format cells to fill downward as in the case of other formulas. What is format painter
 
Thanks Rafael Ortiz, that was really helpful, loving knowing the difference between absolute and relative stuff. This was the exact problem we were having.
 
Back
Top