0

I'm hoping there's an easy answer using conditional formatting, but if not I'm fine with entering in some VBA.

Column A (starting at row 2) has a calculated position (an integer) based on certain criteria. Column B (starting at row 2) has a hard-coded position (an integer).

I have conditional formatting on Column B that will highlight the cell red (the "Bad" style format) if the value in B is less than the value in A. This works great. But I have many of these files and many rows in each file, so I don't want to have to scroll down each file to find a red highlighted cell. What I'd like instead is to highlight cell A1 if ANY of the cells in Column B are highlighted, i.e. are less than their respective A cell.

Is this possible with a conditional format in cell A1? I've tried COUNTIFS() but wasn't able to figure it out, don't know if that's the correct function I'm looking for.

1

If any cell in column B is less than the equivalent cell in column A then:

=MAX(A2:A9999-B2:B9999)>0

will be True

So you can use the formula in the conditional formatting.

EDIT#1:

Enter as an array formula.

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. If this is done correctly, the formula will appear with curly braces around it in the Formula Bar.

  • Getting an error with this one. Even created a new blank file with just A2-A7 and B2-B7 with dummy data (all ints). Pasted your forumla into A1 and get a #VALUE! error – amallard Jul 25 '17 at 17:30
  • 1
    @amallard Got it ! ...................this must be an array formula. – Gary's Student Jul 25 '17 at 17:43
  • 1
    @amallard See my EDIT#1 – Gary's Student Jul 25 '17 at 17:45
  • Never knew about array formulas. Works perfect! – amallard Jul 25 '17 at 17:48
  • @amallard Sorry about the stumble. – Gary's Student Jul 25 '17 at 17:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.