Copy conditional formatting formula to other cells without changing VLOOKUP reference

Hi

I'm trying to copy a conditional formatting formula to other cells in my table however when I paint or copy and paste the reference for my look up table is changed - how can I stop this from happening?

Eg formatting in cell F3
=VLOOKUP(F3,A3:B23,2,FALSE)>VLOOKUP(E3,A3:B23,2,FALSE)

Formatting in cell F4
=VLOOKUP(F4,A3:B24,2,FALSE)>VLOOKUP(E4,A3:B24,2,FALSE)

Each time I copy down the A3:B23 changes to A4:B24 and so on... how do I prevent this?

Thanks

Hi

Make your lookup range Absolute with the $ signs

=VLOOKUP(F3,$A$3$B$23,2,FALSE)>VLOOKUP(E3,$A$3:$B$23,2,FALSE)

Originally Posted by suzy_f

Thanks so much That's worked!

Final Question - I would now like to have a column that counts the number of green / orange / red squares in my table for each row (the colours are determined by the conditional formatting)

How would I do this?

Hi Suzy

Without VBA, you cannot count the colours.
However, what you need to do is to count the cells that match the criteria you have set in order to create the colours.

It would be easier if you could upload a copy of your workbook

How do I mark the thread as resolved as my question was answered? (I thought my second question would fit better in a new thread as it's a new topic).