Color Coding Issue

[pongmeister]

I am using a macro to color code many cells in my worksheet. I have 2 columns which I check values on and then color code cells based on those values. I've used this method in the past and have had no issue.

So I loop thru the specified rows and change cell color based on values in the trigger column. I check AV32:AV169 and set the color using cell offset. This is working fine, however on the second phase I loop thru AX32:AX169
but none of the target cells are affected.

Then I go to cell AX32 and put my cursor at the end of the **formula and hit enter and it fills in the colors as intended. The **formula is present in AX32 to set the color trigger value.

My workbook is too large to load but thought someone may have encountered a similar situation and can advise.

I've copied the macro to a .txt file and attached it to this post.

I have shortened it in the example by eliminating many repetitive value checks.

Thanks for any help...

[6StringJazzer]

If I understand correctly you are saying that your code sets fill colors of some cells based on the values in other cells. When the code loops through AX32:AX169 to determine when to update fill colors, nothing happens. But then if you force a reevaluation of AX32 then the colors all change.

I am not sure what your problem is, but it seems to me that this could all be done with conditional formatting instead of VBA. Hard to help without your file, though.

[pongmeister]

You have stated the problem exactly. I'm ok with changing to conditional formatting which I did use in earlier excel versions (2003), however I'm not sure how to do that in excel 2016. I can try to look at that again but shouldn't the VBA version work also?

Thanks

[pongmeister]

As a follow up:

I did look closer at using conditional formatting and was able to set up the color coding as I want. Thanks again and I will mark this thread solved.