Forum Discussion
excel conditional formatting
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
6 Replies
- 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?
HansVogelaar 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!
