Conditional formatting

LisaGreen
5StarLounger
Posts: 964
Joined: 08 Nov 2012, 17:54

Conditional formatting

Post by LisaGreen »

Hiya,

Apologies for the poor heading!!

I need to use some CF. I want to put a bottm border on a range if a cell in the range is not empty.

I don't use cf all that much so am looking at web sites to try and get my head around it.

On page.. https://techcommunity.microsoft.com/t5/ ... -p/1005636

There is a formula.. =($D1<OFFSET($D1,-1,1))*(ROW()>5)

Is anyone willing to try and talk me through that please?

TIA
Lisa

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Conditional formatting

Post by HansV »

Keep in mind that a conditional formatting rule is applied when the condition is TRUE or any number other than 0.
The formula =($D1<OFFSET($D1,-1,1))*(ROW()>5) combines two conditions. Each of them evaluates to TRUE or FALSE.
If you multiply them, Excel treats TRUE as 1 and FALSE as 0. The only situation in which the product is non-zero is if both conditions are TRUE:

TRUE*TRUE = 1
TRUE*FALSE = 0
FALSE*TRUE = 0
FALSE*FALSE = 0

The first condition is $D1<OFFSET($D1,-1,1). Note that there is a $ before the column letter D, so that is absolute (fixed). But there is no $ before the row number 1, so that is relative (it will change to 2, 3, 4 etc. in the rows below).
OFFSET($D1,-1,1) is the cell one row above and one column to the right of $D1, i.e. the cell in column E in the row above. In row 1, that evaluates to an error, so the condition is not TRUE, but in the rows below, the D2 will be compared to E1, D3 will be compared to E2 etc.

The second condition is ROW()>5. This is FALSE for rows 1 to 5, and TRUE for row 6 and below.

If we combine this, the condition will always be false in rows 1 to 5.
In row 6 and below, the condition will be satisfied if the cell in column D is less than the cell in column E one row up.

Does that help?
Best wishes,
Hans

LisaGreen
5StarLounger
Posts: 964
Joined: 08 Nov 2012, 17:54

Re: Conditional formatting

Post by LisaGreen »

Oh yes it helps tremendously!!

Thank you Hans!!!

Lisa