Forum Discussion
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 marking.
IMAGE below because it didn't let me add one directly.
also i had no clue what to write in the subject since i don't know what it is called.
Hi Wonder,
Sorry for the delay. Have some work. Use the macro below in your excel sheet.
Sub Macro1()
Dim a, f, t As IntegerIf 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 IfIf 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 IfIf 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
9 Replies
i have got it working with marking rules but it stays on group 1 and 2 so i cant use it for the others
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.
- 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
This should be pretty easy to do with a few variables. If an expert doesn't give you a solution, I could probably come up with one for you.
Hi Brandon Gale,
In screen shot, in third box, B specified as 8 to 2, A to B specified as 6 to 9. But wrongly colored in A, B boxes.
In table A, 1 to 5 colored correctly in orange.
Confirm it.
