Conditional format - don't change background color

[Bandito1]

Hi all,

I use conditional formating to color the border of cells that contain a date that is within a week from today.

My table is fill colors; 1 line gray, 1 gray white, 1 line gray and so on.
This is done for readability.

The conditional formating fills all cells with white. I don't want this. I want it to stay either gray or white (the original color).
Some way to make this?

Conditional format formule: =WEEKNUM($D3)=WEEKNUM(NOW())
Format; red borders

Tracker QA on the floor New.xlsm
	A	B	C	D
3512	330640B			26-sep
3513	329853A			28-sep
Tracker QA on the Floor

Cell Formulas
Range	Formula
D3512:D3513	D3512	=IFERROR(INDEX('\\nltilnetapsnas1\PUBLIC\Supply Chain\Planning\Sales Order Report\[SOR.xlsx]SOR (BPOOP011)'!$AC:$AC,MATCH(A3512,'\\nltilnetapsnas1\PUBLIC\Supply Chain\Planning\Sales Order Report\[SOR.xlsx]SOR (BPOOP011)'!$C:$C,0)),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D3:D1048573	Expression	=WEEKNUM($D3)=WEEKNUM(NOW())	text	NO
A3:AG1048573	Expression	=AND($A3<>"";MOD(SUBTOTAL(3;$A$3:$A3);2)=0)	text	NO

 

[Fluff]

Change the format for that rules to "No colour" for the fill.

 

[Bandito1]

"No colour" for the fill removes the gray colour

 

[Fluff]

You need to change the other rule.

 

[jasonb75]

It doesn't make any difference, @Fluff. If you have a structured table with banded rows conditional formatting wipes the table format, even if you set it to no fill.

edit:- 4 rules needed.

=AND(ISODD(ROW($D3));WEEKNUM($D3)=WEEKNUM(NOW()))

=AND(ISEVEN(ROW($D3));WEEKNUM($D3)=WEEKNUM(NOW()))

=AND(ISODD(ROW($A3));$A3<>"";MOD(SUBTOTAL(3;$A$3:$A3);2)=0))

=AND(ISEVEN(ROW($A3));$A3<>"";MOD(SUBTOTAL(3;$A$3:$A3);2)=0))

With the correct fill colours for the table applied as necessary.

 

[Fluff]

Never knew that. Yet another reason to avoid using Tables.

 

[jasonb75]

Yet another reason to avoid using Tables.

Table Pros:- None.
Table Cons:- Run-time error '6': Overflow