Closed
lb_028 Posts 1 Registration date Friday February 13, 2015 Status Member Last seen February 13, 2015 - Feb 13, 2015 at 02:05 AM
vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 - Feb 14, 2015 at 09:28 PM
I need some help that could possibly save me a huge amount of time. I'm new to conditional formatting and am working on a new method to help my staff become more efficient . I'm editing a workbook that a colleague created at his old store.
Here's what I need:
any time someone types in 1, 2, 3, 4 and so on in any of cells H19:H30 I want the corresponding cell (in the screen shot you can see how 1 is N11, 5 is N12, 9 is N14 and 11 is N15) to turn yellow.

My colleague had to do conditional formatting for EVERY H cell for N values of 1-12. (see screen shot for an example)

There has to be an easier way to do this as I had to add an additional 80 lines and want to format it as easy as possible.
I was thinking maybe \$H18:\$H139=1 would work, but it doesn't
Please, can anyone help? Or do i have to do the same thing he did through row 130?

## 1 response

vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Feb 14, 2015 at 09:28 PM
Hello lb_028,

Perhaps this may help:-

- Highlight cells N19:N30
- Go to Conditional Formatting in the Home tab
- Click on New Rule.
- Select "Use a formula to determine which cells to format"
- Type in the following rule:-

=H19>=1

- Format to yellow fill.

Test it by entering the numbers you want in H19:H30.
Enter a zero in any of the range cells to test if the yellow fill returns to white.
Expand the range to suit when you first highlight the cells.

I hope this helps.

Regards,
vcoolio.