Conditional Formatting will not work

Hi,
can someone kindly take a look at this code and figure out why it will not work properly.

It is supposed to format individual cells from one range the same background color as matching cells of another range.

And if a cell from the first range matches two or more cells from the second range, then that cell is formatted black (white font).

Also, AC2 contains a margin value that specifies how far apart ('+' or '-') each value can be from each other (ie. +/-1, +/-1.5, +/-2, etc.).

I really appreciate any help.

I do not see any code?

oops,
forgot to attach the file

I think you might have been making this harder than it needs to be.

The first part of the code worked (it changed the cell to the color you wanted), but since the "If j" had >=1, then this was always true and turned every cell black.

I commented out the SUMPRODUCT, changed the "If j" to be greater than 1, and put a counter for j in the "For Each cell" loop.

Please Login or Register  to view this content.

thanks a lot MSP!

the code work great in the sample, but I keep having the same problem - it will not trigger in the actual workbook. If I click in the cell and hit enter, then only that ONE cell triggers and formats accordingly, nbut the rest the cells in the group remain unaffected.

I also tried inserting it into a "Private Sub Worksheet_BeforeDoubleClick(ByVal Target As range, Cancel As Boolean)" function where I would prefer it to be, but just the same, it will not work. It's a pretty busy workbook, with lots of cell referencing and lookups, but even refreshing the calculations (Ctrl+Alt+F9) does nothing.

Why do you suppose is the problem?

Sorry, I did not understand the original question. (But, I did notice this same "feature" and wondered about it.)

I'll be back in a few minutes.

Added another loop, and reset j each time.

Now, what do you want to happen if you change the Target values (or the Margin), but do not change any of the Variable values?

Please Login or Register  to view this content.

WOW, EXCELLENT!

Thank you very much MSP.

To answer your question, I’m using a 'multiplier' value as a cell validation that changes all the CHECK values simultaneously – I’d like to have the code trigger when that cell is activated, or when the values in the CHECK range change.

Also, using say the MOD function, can you set it so that the numbers 98, 99, 0, 1, etc. are sequential, thus 99 and 0 would be considered a match (within the margin of 1)?

Thanks again for your great help.

Great!

Thanks a lot MSP.

So is it possible to trigger the code via a "BeforeDoubleClick" event and/or a separate cell.

I really appreciate all your help!