Conditional formatting in Excel

[Siddhu11011]

I have applied color coding to cell if the condition is true by using conditional formatting.

Selection.FormatConditions.Add Type:=XlExpression Formula:="=ABS($B$11=$L$2)
Selection.FormatConditions(Selection.FormatConditions.count)SetFirstPriority....and so on

Here B11 cell is changing every time. Instead of fix that, I want active cell to be picked up.

Any solutions please
Thanks in advance

 

[Joe4]

What is the full range you are trying to apply this to?
Can you post the entire block of Conditional Formatting Code?

By the way, I am not so sure that formula is correct.
I think that this:

=ABS($B$11=$L$2)

probably needs to be:

=ABS($B$11)=$L$2

or

=ABS($B$11)=ABS($L$2)

depending on exactly what you are trying to do.

I don't see much point in applying ABS to TRUE or FALSE, which is that the expression ($B$11=$L$2) will return.

 

[Siddhu11011]

What is the full range you are trying to apply this to?
Can you post the entire block of Conditional Formatting Code?

By the way, I am not so sure that formula is correct.
I think that this:

=ABS($B$11=$L$2)

probably needs to be:

=ABS($B$11)=$L$2

or

=ABS($B$11)=ABS($L$2)

depending on exactly what you are trying to do.

I don't see much point in applying ABS to TRUE or FALSE, which is that the expression ($B$11=$L$2) will return.

Apologies for the inconvenience caused from my end. Let me rewrite the code:

Selection.FormatConditions.Add Type:=XlExpression Formula:="=ABS($B$11)=$L$2
Selection.FormatConditions(Selection.FormatConditions.count)SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex=XlAutomatic
.ThemeColor=XlThemeColorAccent6
.TintAndShade=0.399945066682943
End With
Selection.FormatConditions(1).StopIfTrue=False

Would this be sufficient for you?

 

[Joe4]

No, that doesn't answer my question.
Your code starts with "Selection", and we have no idea knowing what range has been selected at that point.

Can you please:
- tell us what range has been selected
- explain exactly what this Conditional Formatting should be checking
- show us some sample data and expected results

 

[Siddhu11011]

No, that doesn't answer my question.
Your code starts with "Selection", and we have no idea knowing what range has been selected at that point.

Can you please:
- tell us what range has been selected
- explain exactly what this Conditional Formatting should be checking
- show us some sample data and expected results

My selected cell is B11 and I'm applying conditional formatting. I want to highlight the cell B11 ( Cell contains positive or negative number) in green if that is matching with value available L2 cell otherwise in red.

Challenge is cell B11 is not fixed everytime. No issues with cell L2.

If activeCell.offset code is at B23 instead of B11 then conditional formatting will not pick right cell. Is there any mechanism to replace B11 with active cell in above conditional formatting formula??

Something like:
XlExpression Formula:="=ABS(ActiveCell)=$L$2p

 

[Joe4]

Try:

Formula:="=ABS(" & ActiveCell.Address & ")=$L$2"

 

[Siddhu11011]

Try:

Formula:="=ABS(" & ActiveCell.Address & ")=$L$2"

I can't express my joy..... Thank you so much. I have been searching for the solution since a week. Thanks again!!

 

[Joe4]

You are welcome.
Glad I was able to help!