Conditional formatting except for a particular value

Question

Hi there

I'd like to colour cells in column A 

1/ IF there is an Invoice number entered

2/ AND the cells in column F are blank  (where the Date Paid has not been entered)

3/ BUT not if the value in column A is 'X'

See table below

In other words, the cells I want coloured are A3, A6 & A7

	A	B	C	D
1	Invoice	Date	Total	Date paid
2	MAT - 45649	08/04/2016	145.80	31/05/2016
3	SKI - SA3293	08/04/2016	9,940.32
4	X
5	ALS - 0317	08/04/2016	3,915.00	11/05/2016
6	SKI - SA3382	18/05/2016	9,558.00
7	SKI - SA3400	31/05/2016	8,841.16
8

Thank you

J

Answer 1

Hello,

select A2 to A7. Create a new conditional formatting rule. Use a formula to determine the format and enter this formula into the formula box.

=AND(LEN(A2)>0,A2<>"x",$D2="")

Select a format and confirm all dialogs.

You talk about column F in your description, but in the data sample the Date Paid is in column D. Change the formula accordingly.

Answer 2

Select A2:A8 (or however far down as you want. I'll assume that A2 is the active cell within the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

=AND(A2<>"",A2<>"X",F2="")

Click Format...

Specify the desired formatting.

Click OK, then click OK again.

Please note: the formula is based on your description, where you mention "the cells in column F are blank".

In your sample data, Date Paid is column D instead of column F, so there the formula should be

=AND(A2<>"",A2<>"X",D2="")

Answer 3

Sorry I meant D2. I'd deleted a column that wasn't needed in the example but forgot to update the question!

Thank you worked well. So did teylyn's

Thank you both