Solved Excel Conditional formatting question

March 18, 2016 at 14:04:02
Specs: Windows 7
I am using conditional formatting to match a value in cell A1 to a list of values. Is there a way to keep the formatted color of the matched cell in the list after I enter another value in A1? i.e. I have a bunch of numbers that I want to verify and show that they all matched the values in the list. It's like a cumulative record, match the entry - turn a color and stay that color.

See More: Excel Conditional formatting question

Report •


✔ Best Answer
March 21, 2016 at 13:29:08
It appears that you changed the requirements given in your original post:

"Is there a way to keep the formatted color of the matched cell in the list after I enter another value in A1?"

"It's like a cumulative record, match the entry - turn a color and stay that color."

Now you are saying that you want a one color for the current match but then have that same cell turn a different color once a new valued is entered in A1? If that's the case, my code won't work.

This code will highlight the entire row, but all in the same color as the other previously matched cells. Do you have the "current match CF" portion already worked out or does that somehow need to be added to the code?

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$A$1" Then
   With Columns("B:C")
     Set c = .Find(Target)
       If Not c Is Nothing Then
         Range(c.Address).EntireRow.Interior.ColorIndex = 6
       Else
         MsgBox "Value Not Found"
       End If
   End With
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
March 18, 2016 at 15:31:14
The easy answer is to use more than just A1. Is that doable?

With your "list of values" in B1:B10, select that range and use this CF formula:

=MATCH(B1,A:A,0)>0

Now enter the numbers you want to verify in any cells in Column A and the matching B cell will be formatted.

If that's not doable, you'll probably need to use a macro. Let me know and I'll throw something together for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 18, 2016 at 15:48:15
Wow, thx for the quick reply.
Ideally all the inputs would be put into cell A1 one-by-one. I want to use a barcode scanner to enter values into A1 of a huge spreadsheet list, I would search for the conditional matched cell, do a separate task, then enter the next value into A1, repeat... When I'm done, all the values in the list will be colored showing they all were verified.

Report •

#3
March 18, 2016 at 15:59:55

Report •

Related Solutions

#4
March 21, 2016 at 08:01:05
The list that I will search thru to is actually in two different columns of the same excel file. It can be hundreds of values that will be in the Primary Value column or in the Alternate Value column.

Report •

#5
March 21, 2016 at 08:21:55
Since you weren't very specific in your answer to my "where" question, I'll just assume Columns B:C for the purpose of this example. Feel free to modify the following code as required.

I'm also assuming that the value in A1 will only be found once in those 2 columns.

That said, I know that this works for manual entry of values in A1 but I have no way of testing it with a barcode scanner. As long as each entry of a value is recognized as a "worksheet change", then the code should work. If it doesn't you'll have to supply some detail as to how the barcode scanner data ends up in A1.

Right-Click the sheet tab for the sheet in which you want this to happen and choose View Code. Paste this code into the pane that opens. Change the the following instruction to reflect your actual columns if required:

With Columns("B:C")

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$A$1" Then
   With Columns("B:C")
     Set c = .Find(Target)
       If Not c Is Nothing Then
         Range(c.Address).Interior.ColorIndex = 6
       Else
         MsgBox "Value Not Found"
       End If
   End With
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
March 21, 2016 at 11:33:58
Thank you, sorry about not being specific on the columns. This WORKS but with a hitch, I have only one occurrence of a value, BUT there can be variations of a value, i.e. RK707G + RK707.
If RK707G is in Column B and RK707 is in Column C and the target is entered as RK707, then RK707G is highlighted in Column B. Can this formula be modified to highlight the entire row where the match is found?
Note I'm using this with conditional formatting to highlight the match in a different color to show where the match is as I'm entering values. Works pretty nice so far, almost there!

Report •

#7
March 21, 2016 at 12:30:11
Some things aren't clear.

re: "Can this formula be modified to highlight the entire row where the match is found?"

The entire row, as in Ax:XFDx or "entire row" as in Bx:Cx?

re: "If RK707G is in Column B and RK707 is in Column C and the target is entered as RK707, then RK707G is highlighted in Column B."

What do actually want highlighted? The cell that contains RK707 as an exact match or all cells that contain RK707 as any part of the cell? (VBA Find works the same as the Excel Find function. It can match entire cell contents exactly or do a "partial find" and find RK707G when searching for RK707. If you want it to find both, it can.)

re: "Note I'm using this with conditional formatting to highlight the match in a different color to show where the match is as I'm entering values. "

You've lost me there. If you are using VBA to highlight the matches and retain the highlighting even after A1 changes, you don't need conditional formatting anymore. The macro actually fills the cell with the chosen color (Interior.ColorIndex = 6) which will never change unless you manually change it.

In other words, CF is not even involved in this process.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
March 21, 2016 at 13:05:04
Thanks again for your reply. Let me step back and explain.

1. What to highlight? Highlight the Row that the match occurs in. i.e. if the match occurs in cell B5 or C5, highlight Row 5, if it occurs in B90 or C90, highlight Row 90. Values are unique enough that there won't be matches in more than one row.

2. Why use CF? CF helps to distinguish the current match from the previously highlighted cells to allow a manual observation. Or, do you have a suggestion how to highlight the currently matched cell in the spreadsheet to distinguish it from the previously matched and highlighted cells?


Report •

#9
March 21, 2016 at 13:29:08
✔ Best Answer
It appears that you changed the requirements given in your original post:

"Is there a way to keep the formatted color of the matched cell in the list after I enter another value in A1?"

"It's like a cumulative record, match the entry - turn a color and stay that color."

Now you are saying that you want a one color for the current match but then have that same cell turn a different color once a new valued is entered in A1? If that's the case, my code won't work.

This code will highlight the entire row, but all in the same color as the other previously matched cells. Do you have the "current match CF" portion already worked out or does that somehow need to be added to the code?

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$A$1" Then
   With Columns("B:C")
     Set c = .Find(Target)
       If Not c Is Nothing Then
         Range(c.Address).EntireRow.Interior.ColorIndex = 6
       Else
         MsgBox "Value Not Found"
       End If
   End With
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
March 21, 2016 at 13:35:43
YES!! That does exactly what I wanted THANK YOU VERY MUCH. I may not have been clear on all I wanted, but you came thru with everything I needed!!

Report •

#11
March 22, 2016 at 15:47:48
There is one bug that is killing me. The numbers I enter into cell A1 all have a leading "P" that confounds this Sub. i.e. Columns B + C do not have the P prefix so I'm trying to get around this by entering the P prefixed number into cell B2 and use a formula in A1 to strip the P off the number. ( I have to scan in the number with a barcode scanner and the number has a leading P, not sure why, but it's there). This leading P is not on any of our lists and we couldn't update all our lists to add a P.
So I thought I could use this formula in A1 :

=RIGHT(B2,LEN(B2)-1)

it removes the leading P and I hoped it would be able to match one of the numbers in columns B or C.
The number in A1 (stripped of the P) should, but doesn't trigger the Sub and doesn't highlight the row. The value in A1 just doesn't seem to be able to match with any numbers in Columns B or C. I thought I was good to go but can't figure this out. (I tried to format the barcode scanner to remove the prefix but it's not that smart.)

Do you know what could be stopping the Sub from being able to match?



Report •

#12
March 22, 2016 at 16:01:05
Try this:

=VALUE(RIGHT(B2,LEN(B2)-1))

The RIGHT() function is a TEXT function
and the result is seen by Excel as TEXT not a Number.

The VALUE() function converts TEXT to NUMBERS

MIKE

http://www.skeptic.com/


Report •

#13
March 22, 2016 at 16:55:25
A Worksheet_Change macro will not fire on a change in a calculated cell, only with a physical change to that cell. Therefore putting a formula in A1 will never work.

Try changing the Set c instruction to this:

Set c = .Find(Right(Target, Len(Target) - 1), lookat:=xlPart)

This basically does what you are doing, except that the code strips off the first character before doing the search allowing you to "physically" change A1 so that the code will fire.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#14
March 29, 2016 at 07:26:21
DerbyDad03: I thought I had followed up with a big THANK YOU VERY MUCH for all your help. You made my task so much easier, in fact you made it possible. I have been doing some minor formatting to dress up my final spreadsheet and trying my hardest to find a crack in the code. But, to my satisfaction, it works everytime without any problems.
Thank you again for your assistance!!
Jerry


Report •

#15
March 29, 2016 at 07:49:49

Report •

Ask Question