Conditional formatting

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

GB - 17 Mar 2008 19:59 GMT

I want to colour code certain cells in a spreadsheet according to which row
they are in: 21, 22, 75 etc etc. I have come up with the following
conditional formatting formula:

=NOT(ISERROR(FIND(RIGHT("00"&ROW(),4),"0021 0022 0075 0144 0149 0261 0262
0264 0387 0388 0389 0390 0391 0392 0548 0549 0563 0576 0614 0650 0690
0719")))

Is there a simpler way of doing this using conditional formatting? I
appreciate that a macro may be better, but I was wondering if there is a
better way to check whether the row number is in a particular list, as this
would have other uses?

Reply to this Message

Pete_UK - 17 Mar 2008 20:20 GMT

You are adding two zeros to the front or your row number and then
truncating it to 4 characters, so this won't work beyond row 9999 or
for rows 1 to 9 - a better way might be:

=NOT(ISERROR(FIND(TEXT(ROW(),"0000"),"0021 0022 0075 0144 0149 0261
0262 0264 0387 0388 0389 0390 0391 0392 0548 0549 0563 0576 0614 0650
0690 0719")))

You can easily add a fifth zero to the format string and extra leading
zeros if you want to cope with more rows.

Another way would be to have a list of the rows you want to highlight,
and then you could use MATCH.

Hope this helps.

Pete

> I want to colour code certain cells in a spreadsheet according to which row
> they are in: 21, 22, 75 etc etc. I have come up with the following
[quoted text clipped - 8 lines]
> better way to check whether the row number is in a particular list, as this
> would have other uses?

Reply to this Message