Conditional formatting restricted to first column

Birdbrain1463

New Member
Joined
Nov 5, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
So I'm pretty sure this is a simple fix but it has got me stumped. I have a data table for which I am trying to run conditional formatting for a whole row when particular criteria is met in the last few columns of the table, however the formatting is only affecting column A.

I am using the "Format based on a formula" function and the formula I am using is as follows:

=OR(AND(NOT(ISBLANK($Q2)),$Q2<S$1,ISBLANK($R2)),AND($P2="Frequently",$Q2<(S$1-7)),AND($P2="Monthly",$Q2<(S$1-23)),AND($P2="Quarterly",$Q2<(S$1-87)),AND($P2="Major Dev",$Q2<(S$1-180))) - I appreciate this might not be the most concise way of writing the formula, I'm afraid my current skills with excel are limited!

Within the "Applies to" box I've put =$A$2:$R$480 (starting from 2 to discount the column titles) to cover the whole table and have also tried just $A:$R but both have the same effect of only applying the formatting to column A. I've also tried changing it to $B:$R and that results in the formatting not showing up at all, so it appears to be something wrong with columns B onwards that just aren't displaying the formatting.

I'm not sure whether there's an option somewhere I've selected that restricts formatting in this way? I've had a look on various help guides but they have told me to set up the formatting function exactly as I have done already.

I have also tried running another conditional formatting rule as a test (format only cells containing the letter "a") and this is working fine, so it's not an inability of the other columns to display formatting, just that for some reason they're not in this case.

What's weird about it is that column A doesn't even feature in the formula I'm running. I would understand more if it was only formatting columns P, Q and R, but it's just column A

Thanks in advance for whoever can help with this, it's been frustrating me more than a little!
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Birdbrain1463

New Member
Joined
Nov 5, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
So I'm pretty sure this is a simple fix but it has got me stumped. I have a data table for which I am trying to run conditional formatting for a whole row when particular criteria is met in the last few columns of the table, however the formatting is only affecting column A.

I am using the "Format based on a formula" function and the formula I am using is as follows:

=OR(AND(NOT(ISBLANK($Q2)),$Q2<S$1,ISBLANK($R2)),AND($P2="Frequently",$Q2<(S$1-7)),AND($P2="Monthly",$Q2<(S$1-23)),AND($P2="Quarterly",$Q2<(S$1-87)),AND($P2="Major Dev",$Q2<(S$1-180))) - I appreciate this might not be the most concise way of writing the formula, I'm afraid my current skills with excel are limited!

Within the "Applies to" box I've put =$A$2:$R$480 (starting from 2 to discount the column titles) to cover the whole table and have also tried just $A:$R but both have the same effect of only applying the formatting to column A. I've also tried changing it to $B:$R and that results in the formatting not showing up at all, so it appears to be something wrong with columns B onwards that just aren't displaying the formatting.

I'm not sure whether there's an option somewhere I've selected that restricts formatting in this way? I've had a look on various help guides but they have told me to set up the formatting function exactly as I have done already.

I have also tried running another conditional formatting rule as a test (format only cells containing the letter "a") and this is working fine, so it's not an inability of the other columns to display formatting, just that for some reason they're not in this case.

What's weird about it is that column A doesn't even feature in the formula I'm running. I would understand more if it was only formatting columns P, Q and R, but it's just column A

Thanks in advance for whoever can help with this, it's been frustrating me more than a little!
P.S. Sorry for saying "formatting" so much o_O
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,900
Office Version
  1. 365
Platform
  1. Windows
What is in the cells that you're testing with ISBLANK()?

The ISBLANK function is badly named and should really have been called ISEMPTY. If the cells contain formulas that show a blank by using "" then ISBLANK will return FALSE.

Also, noting that some of your criteria use things like $Q2<(S$1-3) if Q2 is a formula blank then it will be greater than any numeric value. If it is empty (no formula) then it will be less then any numeric value.
 
Last edited:

Birdbrain1463

New Member
Joined
Nov 5, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
What is in the cells that you're testing with ISBLANK()?

The ISBLANK function is badly named and should really have been called ISEMPTY. If the cells contain formulas that show a blank by using "" then ISBLANK will return FALSE.

Also, noting that some of your criteria use things like $Q2<(S$1-3) if Q2 is a formula blank then it will be greater than any numeric value. If it is empty (no formula) then it will be less then any numeric value.

The data table is to log a record of contacts to a list of potential clients. In column Q I record the date in which a contact attempt was made and in column R I record whether I was able to successfully connect, so neither are governed by a formula, but column Q is set up to record any date on dd/mm/yyyy format.

The focus of the format I am trying to create is to provide a visual prompt (highlight the row entry yellow) when I need to re-connect with the client, hence why I do not want it to highlight clients for which column Q is blank as the prompt is to maintain the client relationship rather than starting a new one.

The $Q2<(S$1-3) is to track when the last attempt to contact exceeds a particular date. In cell S1 I have the formula =TODAY()-7, and it is off of the resulting date that I track how long it has been since I last attempted to contact a client.

I've attached a screenshot of the breakdown I used in writing the formula for reference.

The only cell on the worksheet governed by a formula is S1, all of the cells within the table are either blank or simply have text input.

I don't think it's a formula issue though as the formatting is functioning correctly (i.e. it's obeying all of the rules I've listed in the screenshot attached), however the end result of the highlighted cells is only affecting column A rather than columns A-R.

Contact Formula.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,800
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

It sounds as if you need to lock cell S1 like $S$1 rather than S$1
 
Solution

Birdbrain1463

New Member
Joined
Nov 5, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
That's not fair, how is it that simple a fix!

Thanks very much, it's been driving me up the proverbial wall!

Out of interest though, what's the logic behind that do you know? It seems like such a small change for such a big difference.

I also had to change all of the cell references to 1 rather than 2 in order to get the formatting back onto the correct records, just FYI if anyone reads this at a later date and has a similar problem.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,800
Office Version
  1. 365
Platform
  1. Windows
It only worked for col A because that column was looking at S1, but col B would be looking T1, col C at U1 etc
Also the Q2,R2 etc must be the same as the first row in the applies to range, So with your original range of $A$2:$R$480 they would have been correct.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,297
Messages
5,595,301
Members
413,985
Latest member
daniel1969

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top