PDA

View Full Version : Conditionally format a cell the same as another cell upon a match



zest1
12-14-2006, 08:45 AM
How does one ?borrow? or use the formatting of another cell upon a match? I?d like to evaluate the value of one cell against a group of cells, and if there is a match (within +/-1), then conditionally format the cell the same color as the matching one. But, in case there is more than one cell in the group with a matching value, then color that cell Black (with White font).

Example:
A2 = 15

D2 | E2 | F2 | G2
79 | 14 | 95 | 26
(Rd, Bl, Gr, Ye)

A2 = E2 (+/-1)
Therefore, color A2 the same as E2 (= Blue)

If G2 had the value of 14,15 or 16, then that would be a second match, thus A2 would be formatted Black w/ White font.

Thanks for any help

OBP
12-14-2006, 09:51 AM
When you find the match try this in Visual Basic.

Range("a2").Interior.ColorIndex = ActiveCell.Interior.ColorIndex

for the colour match
Use

Range("a2").Interior.ColorIndex = 1

to set the back colour to black and

Range("a2").Font.ColorIndex = 2

for a white font

JimmyTheHand
12-14-2006, 09:55 AM
For a start this might do. There will be more elegant solutions I guess.

zest1
12-14-2006, 11:04 AM
Thanks guys.

OPB,
I?m not sure how to incorporate those code fragments.

JimmyTheHand,
It appears that only cell C2 is responding to the match and conditional formatting against D3:G3. How do I extend the range form C2 to include say A2:C20

Also, when a new non-matching value is typed into a cell that has previously been conditionally formatted (as a result of a match), the cell should return to unformatted status. it appears that the code is not responding to such non-matching values typed into those previously formatted cells. How do I allow for this?

austenr
12-14-2006, 11:29 AM
See if this past post of mine helps. I think it does the same thing you are looking to do.

http://www.vbaexpress.com/forum/showthread.php?t=4509

Look at Kens answer in the 2nd post.

zest1
12-14-2006, 01:06 PM
thanks Austener, but not quite what I need.

I came up with these two formulas that each uses a static CF'g color. The first CF formula colors cell A2 (black w/ white font) if there are 2 or more matches btwn A2 and D2:G2 that falls within the range of +/-1.

'=OR(COUNTIF($D$2:$G$2,A2-1)>1,COUNTIF($D$2:$G$2,A2+1)>1,COUNTIF($D$2:$G$2,A2)>1)


The second CF formula colors the cell (with a different color) if there is one match btwn A2 and D2:G2 within +/-1 of each other.

=OR(A2=$D$2:$G$2,A2=$D$2:$G$2-1,A2=$D$2:$G$2+1)


The tricky part is converting this to vba and having code format the cell DYNAMICALLY, copying the cell formatting from the matching cell (from D2:G2) to A2, rather than using a designated, static formatting.

austenr
12-14-2006, 01:07 PM
Just a suggestion

JimmyTheHand
12-14-2006, 01:47 PM
I reduced the range, that's actually processed by the Worksheet_Change event, to column A:G, as it was in your example. If you open the code module of Sheet1, you can see and modify this range, as well as the scope of the For Next loop, which does the processing. (Right now Excel is not available for me, that's why I am so vague.)

Jimmy

Zack Barresse
12-14-2006, 02:42 PM
If you have the formula(s) already, why not just use them with the Evaluate() method?

JimmyTheHand
12-15-2006, 12:06 AM
Try as I might, I couldn't do this with COUNTIF. If I only count matching values, I won't know where is the single matching value, whose color should be copied. So I stayed with the loop.

Try the attached file.

Also, be sure to understand these parts of the code.
If Target.Column > 20 Then Exit Sub The macro is launched by change of any cell's value. The line above restricts it's "sensitivity" to cells in columns 1 to 20. You can change this restriction as you wish.
R = Target.Row
Set Rng = Cells(R, 1).Offset(0, 1).Resize(1, 19)
This is where the range to be evaluated is set. Currently it is "Bx:Tx" where x is the rowindex, so in case or row #2, Range ("B2:T2") will be evaluated, and cell A2 formatted, according to the results. You can change this range as well, to anything that makes you sense.

I improved this macro a bit, so that now blank cells and strings in the evaluation range are ignored.

Bob Phillips
12-15-2006, 04:31 AM
Maybe

zest1
12-15-2006, 08:08 AM
thanks for your replies, but again, not quite what I'm looking for. I've attached a wb for you to see what I'm trying to do. It's pretty simple.

I currently have it working using 'static' conditional formatting (one constant color - Green, for all matches). But I'd prefer a more dynamic conditional formatting - to have the color format of the particular cell in D2:G2 copied to A2:B2 upon a match.

(btw, the formatting is per individual cell, not entire rows or columns)

Bob Phillips
12-15-2006, 08:58 AM
I think this is what you are saying

JimmyTheHand
12-15-2006, 09:00 AM
Try this.

zest1
12-15-2006, 10:05 AM
Ok, almost there.

Xld,
typing certain numbers (ie 4) in A2 strangely formats some empty cells - in B36 and C10. This also happens when typing certain numbers in D2:G2.

Jimmy,
Your code seems to format all cells in the worksheet, including D2:G2. I?d like to be able to specify in the code the range of cells that are to receive formatting. For example, in one actual worksheet, only cells beyond Row 7 and beyond Column F are to recieve formatting, whereas in another worksheet, the range would be slightly different. Can you specify a target range of cells in the code, so that it can be adjusted as necessary?

Other than that, it seems to work nicely. Please let me know what to change in the code to fix these tiny bugs.

Thanks!

Bob Phillips
12-15-2006, 10:16 AM
Ok, almost there.

Xld,
typing certain numbers (ie 4) in A2 strangely formats some empty cells - in B36 and C10. This also happens when typing certain numbers in D2:G2.

You have some conditional formtting left over in the workbook. delete them all.

zest1
12-15-2006, 11:21 AM
oops, forgot to remove the old formatting. Works great now.

Btw, how do I transfer the "+/-1" to a cell reference? I tried replacing it with range("J1") but it wouldn't work.

Thank you all for the help! :)

Bob Phillips
12-15-2006, 11:45 AM
Replace it with



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:B" '<== change to suit
Const WS_CHECK As String = "B2:G3" '<== change to suit
Const WS_THRESHOLD As String = "J1" '<== change to suit
Dim cell As Range
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Application.CountIf(Me.Range(WS_CHECK), .Value - Me.Range(WS_THRESHOLD).Value) + _
Application.CountIf(Me.Range(WS_CHECK), .Value) + _
Application.CountIf(Me.Range(WS_CHECK), .Value + Me.Range(WS_THRESHOLD).Value) > 1 Then
.Interior.ColorIndex = 1
.Font.ColorIndex = 2
Else
.Interior.ColorIndex = xlColorIndexNone
.Font.ColorIndex = xlColorIndexAutomatic
For Each cell In Me.Range(WS_CHECK)
If cell.Value = .Value - Me.Range(WS_THRESHOLD).Value Or _
cell.Value = .Value Or _
cell.Value = .Value + Me.Range(WS_THRESHOLD).Value Then
.Interior.ColorIndex = cell.Interior.ColorIndex
End If
Next cell
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

zest1
12-15-2006, 12:07 PM
Perfect!

Thanks a lot. Only thing, I just found another tiny bug.

It seems that the code is using values in A2:B2 along with D2:G2 as the evaluation values. Entering the ?12? in any cell in A:B formats to black (but with black font), since B2 contains 13 and E2 contains 14, thus the code is interpreting those two numbers as a double match (+/-1).

Seems that if there are numbers within +/-1 in both A2:B3 and D2:G3 (rows 2 & 3), then when entering another number in A:B that falls within +/-1 of those, it?s incorrectly handled as a match. I removed all the old formatting and can?t see anything in the code that may be causing this.

hmm :think:

Bob Phillips
12-15-2006, 12:41 PM
Not replicating that problem.

Can you describe it in more detail?

zest1
12-15-2006, 01:16 PM
It appears the code is using the numbers in B2 and B3 to evaluate against the other numbers in that column.

For example, if you enter ?1? or ?12? anywhere in column A or B, you?ll see the cell get formatted to black when it should not, since D2:G2 does not contain the values dictating the formatting. If you enter a number into B2 or B3, and then enter that same number (or a number +/-1 that number) anywhere in columns A or B, then that number somehow gets formatted.

Btw, in case there?s a genuine value of ?0? in D2:G2, to prevent the code from mistakenly equating an empty cell as ?0?, can you insert an if statement like: =IF($A2="","",. (if a cell in column A is empty, then that row will not contain any data).

Thanks!

Bob Phillips
12-15-2006, 01:40 PM
I had a slight range test in error there, this should fix that, and the blanks



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:B" '<== change to suit
Const WS_CHECK As String = "D2:G3" '<== change to suit
Const WS_THRESHOLD As String = "J1" '<== change to suit
Dim cell As Range
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Interior.ColorIndex = xlColorIndexNone
.Font.ColorIndex = xlColorIndexAutomatic
If .Value <> "" Then
If Application.CountIf(Me.Range(WS_CHECK), .Value - Me.Range(WS_THRESHOLD).Value) + _
Application.CountIf(Me.Range(WS_CHECK), .Value) + _
Application.CountIf(Me.Range(WS_CHECK), .Value + Me.Range(WS_THRESHOLD).Value) > 1 Then
.Interior.ColorIndex = 1
.Font.ColorIndex = 2
Else
For Each cell In Me.Range(WS_CHECK)
If cell.Value = .Value - 1 Or cell.Value = .Value Or cell.Value = .Value + 1 Then
.Interior.ColorIndex = cell.Interior.ColorIndex
End If
Next cell
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


BTW, I had this code previously



For Each cell In Me.Range(WS_CHECK)
If cell.Value = .Value - Me.Range(WS_CHECK).Value Or _
cell.Value = .Value Or _
cell.Value = .Value + Me.Range(WS_CHECK).Value Then
.Interior.ColorIndex = cell.Interior.ColorIndex
End If
Next cell


and you changed it to


For Each cell In Me.Range(WS_CHECK)
If cell.Value = .Value - 1 Or cell.Value = .Value Or cell.Value = .Value + 1 Then
.Interior.ColorIndex = cell.Interior.ColorIndex
End If
Next cell


Is this correct?

zest1
12-15-2006, 02:26 PM
Excellent!!!

Thanks a lot for the fix, XLD

Btw, I did not change the code - which one should I use?

Thanks again for your great help!
:beerchug:

Bob Phillips
12-15-2006, 03:49 PM
If you want both conditions to use the variability in J1, then use



For Each cell In Me.Range(WS_CHECK)
If cell.Value = .Value - Me.Range(WS_CHECK).Value Or _
cell.Value = .Value Or _
cell.Value = .Value + Me.Range(WS_CHECK).Value Then
.Interior.ColorIndex = cell.Interior.ColorIndex
End If
Next cell

zest1
12-15-2006, 04:43 PM
Thanks xld.

I?ve been trying to get your code to work in the actual workbook (worksheet module), but since I already have one worksheet change event, it will not allow a second, so I inserted it into the other change event, but it wont work.

Also, I?m using a ?before doubleclick event? (A2) to import/copy the data into the workbook, and the formatting code does not seem to trigger since the individual cells are not directly activated upon copying the data. I tried inserting your code into the before doubleclick event (which is where I?d prefer it be) but it won?t work there either.

I guess I would need the code to trigger upon the doublclick event after the copying of the data, and also when changing the "variability" value in J1. Can you tell me what I need to do to get the code to work in that workkbook?

Thanks a lot.

zest1
12-16-2006, 12:24 PM
I'm very close to getting it working, but I'd like to have the code triggered from A2 (which you call WS_THRESHOLD in the code), rather than the target cells themselves.

Can you please tell me how to do this?

Thanks


I think I see another problem - Excel is still reading decimal values even though I'm using "TRUNC(ROUND" functions - that may be preventing otherwise matches to not get highlighted.

Also, the value in AA2 represents the maximun (+/-) range that will constitute a match btwn two values - not an exact number. How do I get that value to be recognized as a maximum range.

zest1
12-16-2006, 03:29 PM
xld,
why won't this work at all now? :confused2