Dynamic Conditional Formatting

[nada1]

Hi,
I was given some vba code that checks one range of cells against a second range for a match, and conditionally format any cells in the first range the same background color as the cell in the second range.

A third cell is used to hold a variable value (ie. 1, 2, or 3, etc) that determines how far apart (+/-) the match between the two values can be.

The code partially worked in the initial sample workbook, but when I placed it in the actual workbook and changed the target cells, it wouldn’t work at all. (see attatched files)

Can someone please tell me what's wrong with it?

Thanks a lot

[Carim]

Hi,

Besides the code which apparently does not work ..., what is the conditional formatting you are after ...?

Carim

[nada1]

Hi Carim,
not sure I understand your question.

The idea is to have the target cell assume the same background color as the other cell upon a match (that is, a match within the +/- range of the variable value in a third cell).

If you open the attached file labeled "partially working" it'll be clear to you.

[Carim]

Hi,

Input 95 in cell A2 ...
is that what you mean by partially ...

Carim

[nada1]

That's part of it.

The other problem is that the code ignores the variable value in "J2" which determines the distance allowed between the two numbers being evaluated (ie. +/-1, or +/-2, etc.) which constitutes a match.

The third problem is that when I attempt to insert the code into the actual workbook, it doesn't work at all (see the other attached file labeled "not working").

[Carim]

Hi again,

Are you 100% sure about the limits shown in D2 :G3 ...?

Would you agree to have J1 automatically modify these limits ...?

Carim

[Carim]

Hi,

Attached is my understanding of the Basic version of dynamic conditional formatting ( with no fancy +/- or bands that overlap each other ...)

It is a working starting point which you can refine as much as you want ...

HTH
Carim

[nada1]

Hi Carim,

Your code formats all cells, even ones that are not within range of a match, but values falling outside that set range should be ignored and not formatted.

Also, in case a value in A2:B20 has multiple matches (2 or more) with values in D2:G3, then that cell would be formatted black with white font - to indicate 'multiple' matches.

See the attached file with hard-coded "gap range" for the match criteria which actually works perfectly, but stops working when I attempt to specify a different cells target range for use in my actual workbook.

I'm not a vba expert and would really some assistance getting the code to work.

Thanks a lot!

[Carim]

If I may, you explanation as to what is your objective is now a lot clearer ...
(even if 1 appears twice in blue ..)
I will give it a shot ...

Carim

[nada1]

Carim,
just to clarify, there can many of the same values in A2:B20 - that's not important. What's important is if any of those values match the values in D2:G3 (by falling within the set 'gap range').

The gap range (+/-1) was hard-coded in the last sample file, but I'd prefer it to have a cell assignment, if possible.

The key point is the differentiation between single and mutliple matches via the formatting.

For example, if A2 contains say a "20", and a D2 and E2 contain a 19 and 21, then since A2 makes a multiple match with D2 & E2, it would thus be formatted black (white font), whereas if only D2 was the matching value, then A2 would get D2's formatting. Or if D2 and E2 both contain say a 19, then again, that's a multiple match. But if E2 contained 18, that would fall outside the range of a match, leaving D2 as a match - so A2 would be formatted the color of D2.

Hope that makes sense.

Thanks again for helping out


oh, I see what you mean about the two blue 1s. you're right, the code should have formatted those cells black since they each match the "0" and 1" in D2:G3. That's what I mean about the code being inconsistent. I just re-entered those 1s and it worked ok.

[Carim]

Hi,

Because, for example, D2=0 and E2=1, the flexibility provided by a variable margin +/- 1 creates formatting conflicts difficult to solve ...
Indeed, which of these two cells (once the margin is applied) will become the formatting reference ?
Could you please clarify what you need ...?

If I may add a question, for which situation is this process required ?

Carim

[nada1]

Hi Carim,
a Mod function (ie Mod, 100) would solve this problem, so that 100, 0 and 1 become sequential, and flows into the next. In fact that is what I would prefer since, to answer your question, the purpose of this is to highlight various converging and recurring cyclical phenomena throughout certain periods of history (represented as numerical values). And the black cell formatting highlights the convergence of two or more cyclical sequences. I think the Mod function would be the solution for this.

Thanks

[Carim]

Attached is the corrected worksheet ...

HTH
Carim

[nada1]

That’s great, Carim!
Thanks a lot.

Only thing is that when I relocate the cells to where they would be in the actual workbook, and re-assigning those cells in the code, it no longer works correctly, just like before. (see attachment)

Cells that should be formatted the color of the other matching cell are instead highlighted black, and cells whose values fall out of the margin range and should remain unformatted are also highlighted black. It appears that the target cells are being evaluated against themselves rather than against the source cells group.

Also, would you be able to include the Mod (“Mod,99”) function to have the value “0” follow “99” in a cyclical fashion, thus having them be neighboring values? Btw, I changed the “2” to “1” in (J >=2) in the code so that the ‘Margin’ value is less confusing. Was it ok to do that?

Thanks for taking the time to help me with this. :0

[Carim]

Hi,

j has nothing to do with margin ... it represents the sumproduct() test to enable black background ...

reinstating

Please Login or Register  to view this content.

will make macro work again ...

Carim

[nada1]

Hi Carim,

I replaced the 2 in the code, but the problem still remains. If you spend some time entering different values in the target cells, the code eventually stops working, and any values entered (ie 0-10) get mistakenly formatted to all black as though the code is finding multiple matches. Those cells should either be blank since they’re out of range, or they should get the color of the matching cell (ie 6) because of the single match. Delete the cell's values has the formatting correctly go to blank, so the code is at least responding and triggering. But at some point it always stops working and starts evaluating the cells incorrectly. Do you have any idea what may be causing this?

And is there any way to add the Mod function so that "99" is followed by "0" and they are evaluated as neighboring values?

I really appreciate all your help

[nada1]

hi Carim,
were you able to replicate the issues I described in my last post in the last file I upoloaded (2 posts ago)?

is there anything I can do to get it to work more consistently? Also, how do I add the Mod function, to have the values be sequential (ie. 98, 99, 0, 1)?

Thanks for your help.