# Conditional format and highlighting the cell in Excel

• I am trying to highlight anomolies in my data and I have about 400 cells. I am concatinating 2 Access fields and exporting to Excel template. In the excel I want to highlight the cells with discripencies like any decimal -Neg should be highlighted at the same time any cell with 0-Pos should be highlighted.
I could implement 0-Pos conditional format which is stright forward. But how should I implement if there is a number -Neg like attached file.

## Files

• Re: Conditional format and highlighting the cell in Excel

is 0-neg also to be highlighted ?

use an OR and AND to set out a formula

=or(a1="0-pos", and(a1<>"0-neg",right(a1,4)="-neg"))

if -neg is not a decimal so 22-neg should not be highlighted

can you highlight the cells in the example the cond format should be applied

ETAF

• Re: Conditional format and highlighting the cell in Excel

Thank you for responding so quickly. You gave me lot of hope.I am trying to understand the formula but could not figure out to change it.
condition 1) '0-pos' should be higtlighted. Because when 0 is there it can never be positive.
Condition 2) If there is a 'value >0 -Neg' then it should be highlighted, because if there is a number ( decimal or whole) it can never be Negative
Condition 3) value - should be highlighted. if there is a value and no comment like pos or neg that shoudl be highlighted.

Is this possible?
Once again thank you so much.

• Re: Conditional format and highlighting the cell in Excel

=OR(A1="0-pos", AND(A1<>"0-neg",RIGHT(A1,4)="-neg"),ISNUMBER(A1))

so that tests all the conditions , I hope

condition 1) '0-pos' should be higtlighted. Because when 0 is there it can never be positive. A1="0-pos"
Condition 2) If there is a 'value >0 -Neg' then it should be highlighted, because if there is a number ( decimal or whole) it can never be Negative AND(A1<>"0-neg",RIGHT(A1,4)="-neg")
Condition 3) value - should be highlighted. if there is a value and no comment like pos or neg that shoudl be highlighted. ISNUMBER(A1)

see attached

## Files

ETAF

• Re: Conditional format and highlighting the cell in Excel

Works perfectly!!! Thank you so much.
I tried this query =OR(B10="0-pos", AND(B10<>"0-Neg",RIGHT(A1,4)="-Neg") ) but it failed to highlight all the anomolies.
your latest solution is working wonderfully.

• Re: Conditional format and highlighting the cell in Excel

excellent

ETAF

• Re: Conditional format and highlighting the cell in Excel

Thank you for responding so quickly. You gave me lot of hope.I am trying to understand the formula but could not figure out to change it.
condition 1) '0-pos' should be higtlighted. Because when 0 is there it can never be positive.
Condition 2) If there is a 'value >0 -Neg' then it should be highlighted, because if there is a number ( decimal or whole) it can never be Negative
Condition 3) value - should be highlighted. if there is a value and no comment like pos or neg that shoudl be highlighted.

Is this possible?
Once again thank you so much.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!