Highlighting Cells

[TSchanna]

Hello all! Thanks in advance for any help this is my first time posting!

I have a spreadsheet that has a value entered in call it cell A1. The number will be either 8,10,12,14,16,or 20. If 8 is entered I want to highlight cells A2 thru A5. If it is a 10 I would like it to highlight A6 thru A9. And so on. I have been trying Conditional Formatting but just cant seem to get it. I am not opposed in using hidden columns if I have to either. Please let me know any suggestions and again thanks.

PS I also need to copy this all the way thru my sheet down the rows. I also have to have data from other formulas in the cells that I am highlighting. I have a sum cells by color macro VBA running in the background.

 

[Trevor_S]

In cells A2 to A5, start a new rule for conditional formatting - selecting the "Use a formula..." option.

Under "Format values where this formula is true" enter:
=A$1=8
and click on format to select your highlight colour.

Repeat for cells A6 to A9, with the conditional format formula being =A$1=10; and so on, until you get to a cell range where the conditional format formula is =A$1=20.

 

[TSchanna]

In cells A2 to A5, start a new rule for conditional formatting - selecting the "Use a formula..." option.

Under "Format values where this formula is true" enter:
=A$1=8
and click on format to select your highlight colour.

Repeat for cells A6 to A9, with the conditional format formula being =A$1=10; and so on, until you get to a cell range where the conditional format formula is =A$1=20.

This works perfectly. The only thing I need now is to know how you can copy that conditional formatting and paste it down 100 or so rows. Thank you so much.

 

[Trevor_S]

How do you mean by copying it down rows? Do you want the next set of rows to be highlighted when A1 is 22 - or when A26 is 8?

 

[TSchanna]

No. Sorry for the lack of clarification. That works for column A. I want that same for rows B-AX with the highlighting based on the input value from b1, c1, d1, and so on without rewriting the conditional formatting it 100 or so times. I tried to copy and paste but it just wants to merge conditional formatting.

 

[Trevor_S]

It should be possible to select cells a2:a25, copy, then select cells b2:ax25 and paste formats. The conditional format formula have the row number fixed (the "$" in front of the 1), but the column is not fixed. So they should copy across.

If it's not working - check that there's no $ in front of the A.

 

[TSchanna]

It should be possible to select cells a2:a25, copy, then select cells b2:ax25 and paste formats. The conditional format formula have the row number fixed (the "$" in front of the 1), but the column is not fixed. So they should copy across.

If it's not working - check that there's no $ in front of the A.

Works perfectly thank you!