CF issue, 2 cells contain formulas, can't highlight if equal?

[fkoehler]

Hi All, I've done my due diligence and searched the forum before asking, however no luck.

I have 2 cells (B5/B6) which count different columns.
I want to compare their values, and if equal highlight $A$6:$B$6

No matter how I've written the comparison formula ie. =B5=B6, =$B5=$B6, etc, it either does not work, or it highlights but then doesn't de-highlight when I change B5 or B6 to not be equal.

I've confirmed both B5/B6 are Type 1, and have tried encapsulating both B5/B6 Formulas inside Value(), no change.

Here is what my .xls looks like in brief:

A B ( BFormula) ( B Field Type)
5 #Requests 55 =SUMPRODUCT(1/COUNTIF(E1:E2000,E1:E2000&"")) -2 1
6 #Validated 55 =COUNTA(S2:S2000) 1

Current Conditional Format on B6 is:
=$B5=$B6 -Change bkgd color to green. No workee.

Can't remember how I got a similar issue working in the past.
What I want is for A6 & B6 to be highlighted when B5 & B6 are equal.

Oddly enough, if I CF with =$B5, it will highlight B6, but when I make B6 <> B5, B6 shows 54, but stays highlighted....

This is Excel 2013/Win8.1- Fresh install with no addons.

Any help would be greatly appreciated!

 

[fkoehler]

OK, I've never been unable to find an Edit button before. Sorry for the scambling.

Hi All, I've done my due diligence and searched the forum before asking, however no luck.

I have 2 cells (B5/B6) which count different columns.
I want to compare their values, and if equal highlight $A$6:$B$6

No matter how I've written the comparison formula ie. =B5=B6, =$B5=$B6, etc, it either does not work, or it highlights but then doesn't de-highlight when I change B5 or B6 to not be equal.

I've confirmed both B5/B6 are Type 1, and have tried encapsulating both B5/B6 Formulas inside Value(), no change.

Here is what my .xls looks like in brief:

        A                        B                         ( BFormula)                                                                                                                                                                 ( B Field Type)
5       [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Requests"]#Requests[/URL]            55                      =SUMPRODUCT(1/COUNTIF(E1:E2000,E1:E2000&"")) -2                         1
6       [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Validated"]#Validated[/URL]            55                    =COUNTA(S2:S2000)                                                                                                                                         1

Current Conditional Format on B6 is:
=$B5=$B6 -Change bkgd color to green. No workee.

Can't remember how I got a similar issue working in the past.
What I want is for A6 & B6 to be highlighted when B5 & B6 are equal.

Oddly enough, if I CF with =$B5, it will highlight B6, but when I make B6 <> B5, B6 shows 54, but stays highlighted....

This is Excel 2013/Win8.1- Fresh install with no addons.

Any help would be greatly appreciated!

 

[fkoehler]

Sorry, let me try reformatting again...

I have B5 and B6 both counting a different column, correctly
B5 and B6 fields are both Type 1

I'd like B6 to be highlighted when B5=B6.

No matter what I've tried, CF of B6 will not reliably highlight it when B5 does equal B6.
B6 CF using formula =$B5, highlights when equal, however when unequal it does not un-highlight.
Doing any other CF doesn't even highlight B6.



.........................A............................B............( B Formula)............................................................................................ ( B Field Type)

5............... #Requests............ 55.............=SUMPRODUCT(1/COUNTIF(E1:E2000,E1:E2000&"")) -2.............. 1
6.............. #Validated..........55.............=COUNTA(S2:S2000)................................................................................. 1



This is Excel 2013/Win8.1- Fresh install with no addons.

Any help would be greatly appreciated!

 

[fkoehler]

Hi All,

In the event anyone comes across this mess of a thread and figures out they have a similar issue, I finally figured it out.

A5 formula is using a pretty common Column counter f, here: =SUMPRODUCT(1/COUNTIF(E1:E2000,E1:E2000&"")) -2
As you can see above, it shows as 55.
However, when I moved that formula to another column, and then copied That cell and pasted-by-value back to A5, it was actually show 54.9 or something.
So, I simply rounded up like so: =ROUNDUP(SUMPRODUCT(1/COUNTIF(E1:E2000,E1:E2000&"")) -2,0)
Note- I am -2 because of headers and such.
I am not thrilled that Excel decided to roundup on its own.

I've tried formatting cells as General, Number with 2 decimal places and 0 decimals places. It still showed 55 when not roundedup....