# Copying a Conditional Format to other cells...

Closed
Dom - Mar 26, 2015 at 07:07 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - Mar 26, 2015 at 12:12 PM
Hello,

I have set a formula to calculate how many days between two dates i.e. Date in A5 - date in A4 +1 = number of days in A6. Pretty simple ok.

If there is no info in cells A4 & A5, A6 shows "1".

For cosmetic purposes I inserted a conditional format so that if A4 & A5 are empty then text in A6 is white i.e. it looks blank.

I want to copy this conditional form to other rows but when I do so B6,C6,D6,E6 etc are linked to A4 & A5 and not their counter parts i.e. B,C,D,E4 & 5.

Hope this makes sense!?!

Can anyone tell me how to copy the format from Row A to other Rows?

Thanks very much

## 1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 549
Mar 26, 2015 at 12:12 PM
Hi Dom,

That is because of the \$ symbols, they make either column or row absolute.
Try this example for better understanding:
B1: =\$A\$1
Dragging B1 to the left or the right; the formula remains the same.
B1: =A\$1
Dragging B1 to the right will result in =B\$1. Column reference is now relative and will change.
Dragging B1 down will result in =A\$1. Row reference is still absolute and won't change.

But a better solution would be to use a formula which makes the use of conditional format not necessary:
=IF(AND(A4<>"",A5<>""),A5-A4+1,"")

Best regards,
Trowa