Conditional Formatting Rule Not Updating Automatically

Question

Having an issue with a Conditional Formatting Rule not updating automatically when the rule is applied to a cell below another.

I tried to follow another guide and go to Visual Basic under the Developer Ribbon and no changes occurred.

I am using Format Painter to copy the rule down to other cells or using autofill.

The rule is designed to highlight specific cells orange if the POS Type is 22 and the Bill Area <>"UHA". I am trying to update the rule to SEARCH("UHA",XX), but running into issues. There are various formatting differences in names submitted for bill areas within my organization. Some ma have a space, hyphen, etc. Using <>"UHA" was not working for lines that had a hyphen in the name. I found that using the SEARCH() formula worked.

Is there a way to have conditional formatting rules update automatically as they are copied into cells below? The rules are being applied to the correct cells, but the rule is evaluating wrong cells.

Should Display As:

Formula: =AND($X2=22,SEARCH("UHA",$AA2) / Applies to: $W$2:$Z$2

Formula: =AND($Z2=22,SEARCH("UHA",$AA2) / Applies to: $W$2:$Z$2

![](https://learn-attachment.microsoft.com/api/attachments/210405a4-656f-450f-9623-5d52a2726960?platform=QnA

Answer 1

Have you tried this formula？

COUNTIF($C6,"*"&"UHA"&"*")

You can use =0 or =1 to match what you want to apply.

=AND($B6=22,COUNTIF($C6,"*"&"UHA"&"*")=0)

Answer 2

I have used this formula before, the organization that I work for though sometimes has naming conventions were the abbreviation (UHA) is connected with other strings of words with hyphens and Excel is not distinguishing "UHA" with the <>. If this helps I am looking for a property that falls in line with "Does Not Contain".

Example names: UHA-Inpatient HOSP or UHA - Inpatient Hospital

We also use more than a POS Type of 22 and 21, so it is not possible to put <>22 in the formula.

Answer 3

Test it without problem.

=AND($B6=22,$C6<>"UHA")

Answer 4

Hello Snow,

I had a typo in the original response. These cells should not be highlighting if there is a 22 and UHA is listed. Is there a Search Property in Excel similar to <> like I have listed in the original Formula from the screenshot?

Thanks,

Cameron

Answer 5

Hi Cameron,

Greetings! Thank you for posting to Microsoft Community.

What do you mean of that the rule is evaluating wrong cells?

Test from myside, it seems all worked as expect. I copy the rows which has the condition formatting and paste after the end, the rule applied automatically.

Best Regards,

Snow Lu