Forum Discussion
Eddy_Megens
May 08, 2021Copper Contributor
Dropdown menu with specified colors and still free text in that colored cell
Hello, I know how to create a dropdown menu in Excel with text and a color with text but the text can not be changed. What I do not know is how to create a dropdown menu with predefined/specified...
- Dec 17, 2024
When you open the advanced permission settings, click Grant Permission on the ribbon. Then, add your distribution list (such as “Everyone Except External Users”) and grant it direct access, rather than including it in any default groups. Once direct access has been assigned, remove the DL from any default groups, such as “Members, Visitors or Owners".
This direct access configuration is automatically applied at the site level. Although the modern experience interface only shows the three default SharePoint groups, any direct access assigned through the advanced permission settings in the classic experience is still in effect. In other words, what you see in the modern experience does not negate the permissions you’ve already set through the classic interface.
Eddy_Megens
May 09, 2021Copper Contributor
Hello Hans,
thanks and I followed your suggestion but it does not work yet. I have copied the screens to a document to clearify. So if you want to check me please send me your email address. By the way I am living in Holland Regards
thanks and I followed your suggestion but it does not work yet. I have copied the screens to a document to clearify. So if you want to check me please send me your email address. By the way I am living in Holland Regards
HansVogelaar
May 09, 2021MVP
Thank you for your email message. It turns out that I had misinterpreted your request. Unfortunately, it's not possible to do what you want using conditional formatting - if a cell doesn't meet the condition, the rule will not be applied. You need VBA code instead.
See the attached workbook. It is a macro-enabled workbook, so you will have to allow macros when you open it.
Code in the worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
Dim rng As Range
If Not Intersect(Range("D2:D14"), Target) Is Nothing Then
' Loop through the modified cells in D2:D14
For Each cel In Intersect(Range("D2:D14"), Target)
' Does the value occur in the list of colors?
Set rng = Range("A2:A6").Find(What:=cel.Value, LookAt:=xlWhole)
If Not rng Is Nothing Then
' User selected a color from the list, so copy the color
cel.Interior.Color = rng.Interior.Color
Else
' User entered text manually; leave color as it is
End If
Next cel
End If
End Sub
- PefterakosJun 12, 2024Copper ContributorThank you very very much!! Have a nice summer!!
- HansVogelaarJun 11, 2024MVP
The Data Validation rule has been extended to B2:E14, and the code in the worksheet module of the attached workbook refers to this range too.
- PefterakosJun 11, 2024Copper ContributorHello,
I tried to apply your code for more colums (b, c, d, e) but i got lost.. Could you give me an assist?
Thanx in advance - Eddy_MegensMay 10, 2021Copper ContributorHello Hans,
many many thanks because this is the solution I was searching!!!!