Color a cell upon entry of specific data

[Mooncake]

I would like to color a range of cells a particular color upon entry of 1 of 6 identifiers: o, o!, o#, y,y!, and y#.
I do not wish to use conditional formatting, because I want to assign the code to other buttons for other users (unique coloring system).
I have the following code, but it seems I could pare it down a bit. Any suggestions on streamlining?
Thanks!

Sub Set_Range_And_Color__B1()
'
' Set_Range_And_Color_B1 Macro
'
Range("A3:Z70").Select
Range("A3").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""o"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""o!"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""o#"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""y"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""y!"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""y#"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

 

[DanteAmor]

I sent you the macro for the conditional format in post # 14

https://www.mrexcel.com/forum/excel...multiple-users-group-sheet-2.html#post5256563

if you want I can share my test file:

https://www.dropbox.com/s/s86x2rcdt8eegf4/buttons.xlsm?dl=0

 

[Mooncake]

I tried the following:

Sub Bill()
Call PutColor(5) 'blue
End Sub
Sub Brett()
Call PutColor(3) 'red
End Sub
Sub Sarith()
Call PutColor(6) 'yellow
End Sub
Sub Sergio()
Call PutColor(4) 'green
End Sub
Sub button5()
Call PutColor(0) 'no fill
End Sub
Sub PutColor
Cells.FormatConditions.Delete
Range("A1").Select
With Range("$A$4:$Z$70")
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=O(A1=""o"",A1=""y"",A1=""o!"",A1=""y!"",A1=""o#"",A1=""y#"")"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
.FormatConditions(1).Interior.ColorIndex = n
.FormatConditions(1).StopIfTrue = False
End With
End Sub

This does not appear to do anything

 

[DanteAmor]

If you are testing with my file, cells B4 and B10 change color with each button.
Just add more: o, y, o !, y! , or# and# in the range of cells "$A$1:$G$50"

Just perform the test without modifying the macro, once you know the process, then make all the changes to the macro, if you want to tear it apart.

 

[Mooncake]

I tried the code you pasted on the other.

Sub button1()
Call PutColor(5) 'blue
End Sub
Sub button2()
Call PutColor(3) 'red
End Sub
Sub button3()
Call PutColor(6) 'yellow
End Sub
Sub button4()
Call PutColor(4) 'gree
End Sub
Sub button5()
Call PutColor(0) 'no fill
End Sub
'
Sub PutColor
Cells.FormatConditions.Delete
Range("A1").Select
With Range("$A$1:$G$50")
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=O(A1=""o"",A1=""y"",A1=""o!"",A1=""y!"",A1=""o#"",A1=""y#"")"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
.FormatConditions(1).Interior.ColorIndex = n
.FormatConditions(1).StopIfTrue = False
End With
End Sub

Does not work. I tried to go to the file in DropBox, and it is returning a 404 error. I really do appreciate your help, but this is REALLY frustrating

 

[DanteAmor]

https://www.dropbox.com/s/s86x2rcdt8eegf4/buttons.xlsm?dl=0

https://www.dropbox.com/s/hvgqpaqmymdj9gz/buttons2.xlsm?dl=0

It's the first time I have problems sharing a file. I leave you a couple of links.

It's frustrating for me too.

 

[DanteAmor]

only add to each button one of the macros:
Sub button1()

Sub button2()


Sub button3()


Sub button4()


Sub button5()

 

[Mooncake]

This is what I have, but I'm missing something...

Private Sub ToggleButton1_Click()
If Me.ToggleButton1.Value = False Then Exit Sub
Me.ToggleButton2.Value = False
Me.ToggleButton3.Value = False
Me.ToggleButton4.Value = False
Me.ToggleButton5.Value = False
Call PutColor(5)
End Sub
Private Sub ToggleButton2_Click()
If Me.ToggleButton2.Value = False Then Exit Sub
Me.ToggleButton1.Value = False
Me.ToggleButton3.Value = False
Me.ToggleButton4.Value = False
Me.ToggleButton5.Value = False
Call PutColor(3)
End Sub
Private Sub ToggleButton3_Click()
If Me.ToggleButton3.Value = False Then Exit Sub
Me.ToggleButton1.Value = False
Me.ToggleButton2.Value = False
Me.ToggleButton4.Value = False
Me.ToggleButton5.Value = False
Call PutColor(6)
End Sub
Private Sub ToggleButton4_Click()
If Me.ToggleButton4.Value = False Then Exit Sub
Me.ToggleButton1.Value = False
Me.ToggleButton2.Value = False
Me.ToggleButton3.Value = False
Me.ToggleButton5.Value = False
Call PutColor(4)
End Sub
Private Sub ToggleButton5_Click()
If Me.ToggleButton5.Value = False Then Exit Sub
Me.ToggleButton1.Value = False
Me.ToggleButton2.Value = False
Me.ToggleButton3.Value = False
Me.ToggleButton4.Value = False
Call PutColor(0)
End Sub
'
Sub PutColor
Cells.FormatConditions.Delete
Range("A1").Select
With Range("$A$1:$G$50")
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=O(A1=""o"",A1=""y"",A1=""o!"",A1=""y!"",A1=""o#"",A1=""y#"")"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
.FormatConditions(1).Interior.ColorIndex = n
.FormatConditions(1).StopIfTrue = False
End With
End Sub

 

[DanteAmor]

Could not download any of my files?