Conditional Formatting For Multiple Users On A Group Sheet

Mooncake

New Member
Joined
Apr 6, 2019
Messages
27
Super new here, but I've been dabbling with Excel for a couple of years.

I've been tasked with creating a group sheet.
Clicking 1 of 5 buttons will automatically color the background of a cell, according to the button clicked, if I enter 1 of the 6 identifiers.

This is the kind of flow I was thinking:

Button 1=Blue
Button 2=Red
Button 3=Yellow
Button 4=Green
Button 5=NoFill

If Button 1 on, then Buttons 2, 3, and 4 off.
If a cell color already equals 2,3, or 4, then background of new cells change to Blue if it contains "o", "y", "o!", "y!","o#", "y#"


If Button 2 on, then Buttons 1, 3, and 4 off.
If a cell color already equals 1,3, or 4, then background of new cells change to Red if it contains "o", "y", "o!", "y!","o#", "y#"


If Button 3 on, then Buttons 1, 2, and 4 off.
If a cell color already equals 1,2, or 4, then background of new cells change to Yellow if it contains "o", "y", "o!", "y!","o#", "y#"


If Button 4 on, then Buttons 1, 2, and 3 off.
If a cell color already equals 1,2, or 3, then background of new cells change to Green if it contains "o", "y", "o!", "y!","o#", "y#"


If Button 5 on, then Buttons 1, 2, 3, and 4 off.
All conditional formatting is turned off (normal sheet status)

:confused: Is this even possible???
Any help would be greatly appreciated. Thanks!
 
I'm well aware the user can change the color of the cell. It's just going to be a lot of data, and I'm trying to cut down on repetition as best as I can.
Can CF be toggled by a button or no?
 
Upvote 0

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Test with cell B4, put the value y# in cell B4, assign each macro to its respective button and press one at a time so you can see the effect in the cell.
Comment if it is what you need.

Code:
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(n)
   Dim cell As Range
   Set cell = Range("B4")
   Select Case cell.Value
      Case "o", "y", "o!", "y!", "o#", "y#"
         cell.Interior.ColorIndex = n
   End Select
End Sub
 
Upvote 0
Could you elaborate? It's frustrating to try to understand the difference if you reply with the same answer without context.
Thanks
 
Upvote 0
With CF.

Change $A$1:$G$50, for the desired range of cells.


Code:
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(n)
    Cells.FormatConditions.Delete
    Range("A1").Select
    With Range("[COLOR=#ff0000]$A$1:$G$50[/COLOR]")
      .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


I attach my file so you can try it

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

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top