Rainbow conditional formatting for a range based of date...

You cant use a range in an AND statement like that, try this instead
=AND(A1=$L$13,K1>=INT($K$10))
Also, if you want this to apply to all cells in that range, dont use $

Hello jackf-nc,

The $ prevents the row or the column to change, depending on where it is in the cell reference.

For example in the formula Ford provided, $K$10 means even if you drag the formula up, down, left or right, the cell reference will not change. Same goes to $L$13.

If you want the cell reference to change when you drag it left and right, but not up and down (I assume that this is what you want), change $L$13 to L$13 instead (Locking the row reference).

If you want to do it the other way around, change it to $L13 (locking the column reference).

Edit: You should also change the reference for K1 too - $K1 to lock the column / K$1 to lock the row / $K$1 to lock both row and column.

An example is

Please Login or Register  to view this content.

Applying to L1:P6 in your example

OK based on your instructions in post 1...

I currently have 5 columns that have numbers a-e. Column K holds a date past or future.... K10 is today's date. I need cells in a-e only to display color if date is in future and based off cells L14:P14

I have tried using formula(and statement) but what happens is that it changes all cells to one color, or no color at all. I have attempted to clear all formatting but same results. I have posted a sample including cell a1 that is blank but should not be based on date and l11
If I just use cell equals then point (l:p) it works, but not based off date - I hope that makes sense.

I have adjusted the CF formula to get the 1st row "correct". What I often do when working with a complex CF formula is to construct it IN the worksheet. CF only works on TRUE or FALSE (or 1/0), so you just need to construct the formula to return that....

	A	B	C	D	E
1	11	12	23	25	32
2	3	16	35	37	51
3	18	20	27	31	38
4	5	25	32	56	64
5	8	12	20	37	67
6	5	7	11	36	65
7
8
9	TRUE	TRUE	TRUE	TRUE	TRUE
10	FALSE	FALSE	FALSE	FALSE	FALSE
11	FALSE	FALSE	FALSE	FALSE	FALSE
12	FALSE	FALSE	FALSE	FALSE	FALSE
13	FALSE	TRUE	FALSE	FALSE	FALSE
14	FALSE	FALSE	FALSE	FALSE	FALSE

A9=AND(A1=L$13,$K1>=K$10)
copied across and down.
This is the formula I used in CF and the 1st row looks like it worked.
(you dont need INT() btw, the way you are using it, it strips out the decimal, but you have only dates that have no decimal)

Now, Im not sure what that 1st part is supposed to be doing, or what it is supposed to be referencing?
=AND(A1=L$13,$K1>=K$10)