Forum Discussion

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
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

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

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
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?
- Wonder Steak
  Copper Contributor
  Apr 17, 2018
  thats perfect.
  - Logaraj Sekar
    Iron Contributor
    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