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.

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
5
  • 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
    Commented Jul 25, 2017 at 17:30
  • 1
    @amallard Got it ! ...................this must be an array formula.
    – Gary's Student
    Commented Jul 25, 2017 at 17:43
  • 1
    @amallard See my EDIT#1
    – Gary's Student
    Commented Jul 25, 2017 at 17:45
  • Never knew about array formulas. Works perfect!
    – amallard
    Commented Jul 25, 2017 at 17:48
  • @amallard Sorry about the stumble.
    – Gary's Student
    Commented Jul 25, 2017 at 17:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .