Forum Discussion

Wonder Steak's avatar
Wonder Steak
Copper Contributor
Apr 12, 2018
Solved

marking data

Hello everybody, i'm looking for a way to automatically mark the numbers in the groups ( will be 10-15 groups in total ) with a color whenever i change the numbers on the right with group to group ma...
Macros & VBA
Requests
  • Logaraj Sekar's avatar
    Logaraj Sekar
    Apr 20, 2018

    Hi Wonder,

     

    Sorry for the delay. Have some work. Use the macro below in your excel sheet.

     

    Sub Macro1()
    Dim a, f, t As Integer

    If Range("M3").Value = "A" Then
    f = Range("N3").Value
    t = Range("O3").Value
    For i = f To t
    Range("P3").Select
    Selection.Copy
    Range("A3:C8").Select
    Selection.Find(What:=i, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Offset(-1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Next i
    End If

    If Range("M4").Value = "B" Then
    f = Range("N4").Value
    t = Range("O4").Value
    For i = f To t
    Range("P4").Select
    Selection.Copy
    Range("E3:G8").Select
    Selection.Find(What:=i, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Offset(-1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Next i
    End If

    If Range("M5").Value = "A to B" Then
    f = Range("N5").Value
    t = Range("O5").Value
    For i = f To 18
    Range("P5").Select
    Selection.Copy
    Range("A3:C8").Select
    Selection.Find(What:=i, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Offset(-1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Next i
    For i = 1 To t
    Range("P5").Select
    Selection.Copy
    Range("E3:G8").Select
    Selection.Find(What:=i, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Offset(-1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Next i
    End If
    End Sub

Wonder Steak's avatar
Wonder Steak
Copper Contributor
Apr 15, 2018

i have got it working with marking rules but it stays on group 1 and 2 so i cant use it for the others

  • Logaraj Sekar's avatar
    Logaraj Sekar
    Iron Contributor
    Apr 15, 2018

    Hi Wonder Steak,

     

    Just clear my doubts of the following.

    1. Specify the range of groups clearly. (Eg. First group A3:C6, Second Group E3:G5, etc)

    2. In which range you want to put condition, Coz based on that data only you result will be displayed. So please mention that range.

     

    Just send that sample excel file. I will put formula. Then use it.

    • Wonder Steak's avatar
      Wonder Steak
      Copper Contributor
      Apr 15, 2018
      i cant access it for the next few days.

      A3:C8 (Group1)
      E3:G8 (Group2)
      I3:K8 (Group3)
      A10:C15 (Group4)
      E10:G15 (Group5)
      I10:K15 (Group6)

      there wont be anything else in the range of the groups so A3:K15
      • Logaraj Sekar's avatar
        Logaraj Sekar
        Iron Contributor
        Apr 16, 2018

        Hi Wonder,

         

        See the screen shot. I just made it for A and B. Is this okay for you? if yes means, i will proceed further remaining groups.

         

        Refer Range "M3:P5". Is this enough for put your conditions or you want to add more?

Resources