excel conditional formatting

[jensor4]

I'm trying to highlight a range of cells in a row when data is entered in a particular cell in that row.

For example:

Select A1:D1        This is the range I want to change color

Select "Conditional Formatting"

Select "New Rule"

Style: Select "Classic"

Select "Use formula to determine which cells to format"

Enter formula: =D1>-.1     This is the cell that I want to change color of range A1:D1 when number >= -.1 is entered in D1.

Select "OK"

Result: Now when I enter data in D1only A1 changes color.

So the question becomes how to select a range that gets the conditional formatting.

See screen shot

Jack Ensor

[#2]

@jensor4 

Change the formula to

=$D1>-0.1

The $ makes the reference to column D absolute (fixed).

[#3]

@Hans Vogelaar Thanks to your answer, I was able to arrive at a solution. Here was my solution:

First you must select the entire range that you want involved. Normally in my case I don't know how many rows I'm going to need so it would be best to select enough that you think you will be covered. I will select a range covering 1000 rows.

Select A1:D1000        This is the range I want to change color

Select "Conditional Formatting"

Select "New Rule"

Style: Select "Classic"

Select "Use formula to determine which cells to format"

Enter formula: =$D1>0    This is the cell that I want to change color of range A1:D1 when a number >0 is entered in D1.

Select "OK"

Result:

If I enter a number >0 in any cell in column D up to D1000 it will format the corresponding row.

Thanks again!

[#4]

Actually, my solution is not the best, because if I enter 0, it doesn't highlight, Therefore I need to enter a very small number such as .00001 and if the numeric format is set to display 2 decimals it displays as 0.00. and high-lights as it should. Could there be another approach to make it high-light when I enter zero?

[#5]

@jensor4 

Perhaps

=$D1>=0

[#6]

No that doesn't work. It makes all cells immediately to format to the selected color.

[#7]

@jensor4 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?