Highlight Data Based on Data in Another Column

[JohnYuhaschek]

How can I highlight data based on another cell in the same row?

Ex. I only want to Select data in Column C only if Column B = Rm2


A B C D
1 Bob Rm1 x 0
2 Jack Rm1 x 0
3 Dean Rm2 n 0
4 John Rm2 n 0
5 Barb Rm3 x 0




https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[JohnYuhaschek]

I asked the wrong question

How can I Select data based on another cell in the same row?

Ex. I only want to Select data in Column C & D only if Column B = Rm2. I will be doing a find and replace after the info has been selected. I will be using this in a macro.


A B C D
1 Bob Rm1 x 0
2 Jack Rm1 x 0
3 Dean Rm2 n 0
4 John Rm2 n 0
5 Barb Rm3 x 0

[LalitPandey87]

We can select Column C & D based on Column B with the help of find method in VBA but what next. :confused:

[JohnYuhaschek]

I only need to select the data in Columns C and D where Column B equals the room number chosen.

So for this example for Rm1, I only need to Select the information in Columns C and D in rows 1 and 2.
If I needed to select Columns C and D for Rm2, I only need to Select the information in Columns C and D in rows 3 and 4.
The macro would almost work like conditional formatting where the data would be highlighted based on a condition in the row.

I hope I explained it better.

[bakerman]

You state that you want to perform a Find & Replace with selected items.
If you can explain a little more what you want to replace the selected items with you can perform this in 1 action so there's no need to really select them first.

[JohnYuhaschek]

A B C D
Bob Rm1 x 0
Jack Rm1 x 0
Dean Rm2 n 0
John Rm2 n 0
Barb Rm3 x 0

I only want to select the data in Columns C and D where the Rm# in each of the rows of data = Rm1

Example:
Premise: If I wanted to select the data from Column C and D with Column B = Rm1
Solution: I would select Columns C and D in rows 1 and 2.

I'm going to replace the x in Column C with "n/a" and the 0 in Column D with "j/k"

[bakerman]

We can start with this one.

Sub FindReplace()
FindNr = InputBox("Roomnumber to search", "Replace data")
With Sheets("Sheet1")
For Each cl In .Range("B1:B" & .Cells(Rows.Count, 2).End(xlUp).Row)
If cl.Value = FindNr Then
cl.Offset(, 1) = IIf(cl.Offset(, 1).Value = "x", "n/a", cl.Offset(, 1).Value)
cl.Offset(, 2) = IIf(cl.Offset(, 2).Value = 0, "j/k", cl.Offset(, 2).Value)
End If
Next
End With
End Sub

[JohnYuhaschek]

This code for Sub FindReplace() worked.
Is there anyway to use a different tab (Containing a Location and Room #), within the same workbook, to Use instead of the InputBox. So Instead of having to put a Room # in the input box, is there a way to specify a Location (In a particular Cell) and pull the Room # from the TableTab? Then use the Room # pulled (from the table) run the Find and Replace macro?

[bakerman]

Post a new example file which represents the current situation.
Be sure to explain exactly where what is to be found, which cell to be used to specify location, etc...
The more information you provide the better we can help you.

[JohnYuhaschek]

This code for Sub FindReplace() worked in the attached document.


Sub FindReplace()
FindNr = InputBox("Roomnumber to search", "Replace data")
With Sheets("Sheet1")
For Each cl In .Range("B1:B" & .Cells(Rows.Count, 2).End(xlUp).Row)
If cl.Value = FindNr Then
cl.Offset(, 1) = IIf(cl.Offset(, 1).Value = "x", "n/a", cl.Offset(, 1).Value)
cl.Offset(, 2) = IIf(cl.Offset(, 2).Value = 0, "j/k", cl.Offset(, 2).Value)
End If
Next
End With
End Sub

Is there anyway to use a different tab (Containing a Location and Room #), within the same workbook, to Use instead of the InputBox. So Instead of having to put a Room # in the input box, is there a way to specify a Location (In a particular Cell) and pull the Room # from the TableTab? Then use the Room # pulled (from the table) run the Find and Replace macro?

[bakerman]

Start with this. Put it in the Sheet Example module.
Since you are not clear about which values have to be changed to what you must adapt the code yourself.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then
FindNr = Sheets("Table").Columns(1).Find(Target.Value, , xlValues, xlWhole).Offset(, 1).Value
With Sheets("Example")
For Each cl In .Range("B4:B" & .Cells(Rows.Count, 2).End(xlUp).Row)
If cl.Value = FindNr Then
cl.Offset(, 1) = IIf(cl.Offset(, 1).Value = "x", "n/a", cl.Offset(, 1).Value)
cl.Offset(, 2) = IIf(cl.Offset(, 2).Value = 0, "j/k", cl.Offset(, 2).Value)
End If
Next
End With
End If
End Sub

[JohnYuhaschek]

Bakerman,

Per your Code,


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then
FindNr = Sheets("Table").Columns(1).Find(Target.Value, , xlValues, xlWhole).Offset(, 1).Value
With Sheets("Example")
For Each cl In .Range("B4:B" & .Cells(Rows.Count, 2).End(xlUp).Row)
If cl.Value = FindNr Then
cl.Offset(, 1) = IIf(cl.Offset(, 1).Value = "x", "n/a", cl.Offset(, 1).Value)
cl.Offset(, 2) = IIf(cl.Offset(, 2).Value = 0, "j/k", cl.Offset(, 2).Value)
End If
Next
End With
End If
End Sub

How do I get Excel to recognize this code as a macro? I have enabled excel to allow for all macros in my settings.

[bakerman]

As I said earlier >> Put the code in the Sheet Example module not in a normal module.
As soon as you change B1 in Sheet Eample (confirm with Enter) the code starts automatically.