Birdbrain1463
New Member
 Joined
 Nov 5, 2020
 Messages
 4
 Office Version

 365
 Platform

 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$17)),AND($P2="Monthly",$Q2<(S$123)),AND($P2="Quarterly",$Q2<(S$187)),AND($P2="Major Dev",$Q2<(S$1180)))  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!
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$17)),AND($P2="Monthly",$Q2<(S$123)),AND($P2="Quarterly",$Q2<(S$187)),AND($P2="Major Dev",$Q2<(S$1180)))  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!