Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Highlight Data Based on Data in Another Column

  1. #1
    Junior Member
    Join Date
    Jan 2014
    Posts
    7
    Rep Power
    0

    Highlight Data Based on Data in Another Column

    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
    Attached Files Attached Files
    Last edited by DocAElstein; 07-07-2023 at 12:55 PM.

  2. #2
    Junior Member
    Join Date
    Jan 2014
    Posts
    7
    Rep Power
    0
    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

  3. #3
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    We can select Column C & D based on Column B with the help of find method in VBA but what next.

  4. #4
    Junior Member
    Join Date
    Jan 2014
    Posts
    7
    Rep Power
    0
    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.

  5. #5
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    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.

  6. #6
    Junior Member
    Join Date
    Jan 2014
    Posts
    7
    Rep Power
    0
    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"

  7. #7
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    We can start with this one.
    Code:
    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

  8. #8
    Junior Member
    Join Date
    Jan 2014
    Posts
    7
    Rep Power
    0
    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?

  9. #9
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    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.

  10. #10
    Junior Member
    Join Date
    Jan 2014
    Posts
    7
    Rep Power
    0
    This code for Sub FindReplace() worked in the attached document.

    Code:
    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?
    Attached Files Attached Files
    Last edited by Admin; 04-17-2014 at 07:52 AM.

Similar Threads

  1. Replies: 34
    Last Post: 03-13-2015, 02:26 PM
  2. Replies: 10
    Last Post: 02-18-2014, 01:34 PM
  3. Group And Transpose Data Based On Sections of Data
    By theladysaphir in forum Excel Help
    Replies: 5
    Last Post: 06-28-2013, 07:55 AM
  4. Replies: 17
    Last Post: 05-22-2013, 11:58 PM
  5. Group Pivot Data Based On Row Values In One Column
    By mrmmickle1 in forum Excel Help
    Replies: 10
    Last Post: 10-09-2012, 11:46 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •