CODEWITHSUNDEEP
excellogicconditional-statementsformulaconditional-formatting
davidlimjh
davidlimjh

Reputation: 3

Excel Formula: Conditional Format if additional values are present

Here is what i need to achieve:

I want to change the format of a cell if it consists of a value other than Administrator or Authenticated User. If it contains both Administrator and Authenticated user, the format is not changed. However if it contains anomalies, such as "Everyone" or "Backup Operators" the cell is flagged. Basically if the cell contains anything other than Administrator or Authenticated User, the cell's format is changed.

Examples:

Administrator, Authenticated User           - Format Unchanged
Administrator                               - Format Unchanged
Authenticated User                          - Format Unchanged
Administrator, Authenticated User, Everyone - Format Changed
Everyone                                    - Format Changed

I have tried multiple methods to solve this, but they don't seem to be working. I use formulas to determine the cell formatting under Conditional Formatting.

=AND(NOT(ISERROR(SEARCH("Admin","CELL"))), NOT(ISERROR(SEARCH("Auth","CELL"))))

=NOT(OR(NOT(ISERROR(SEARCH("Admin",F132))), NOT(ISERROR(SEARCH("Auth",F132)))))

Now I am stuck and I have no idea how to proceed. Any help would be appreciated!

Upvotes: 0

Views: 688

Answers (2)

bamblack
bamblack

Reputation: 3779

The solution provided by @user2140261 works great. However, if you want a more dynamic solution where you don't have to add all the possibilities that it could be other than "Administrator" and "Authenticated User" then you could try this formula

=IF(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"Administrator",""),"Authenticated User",""),", ",""),"ANOTHER NAME HERE", ""))>0,TRUE,FALSE)

Breaking down that formula from the inside out

  • You use SUBSTITUTE to replace any occurrences of Administrator with "" in your source cell
  • You use SUBSTITUTE to replace any occurrences of Authenticated User with "" in the result above
  • You use SUBSTITUTE to replace any occurrences of ", " with "" in the result above
  • You use LEN to test the length of the result above, if it's greater than 0 then something other than Administrator or Authenticated User is in the cell.

Which results in 

Administrator, Authenticated User              FALSE
Authenticated User                             FALSE
Authenticated User, Administrator              FALSE
Authenticated User, Administrator, Everyone    TRUE
Everyone                                       TRUE

Where when it's true it means that the format needs to change.

edit To account for you wanting to add/remove more values now and to be able to customize it easily, it might be easiest to do a UDF:

Public Function SUBSTITUTEARRAY(ByVal Source As Range, ByVal Substitute As Range) As String

    Dim c       As Range
    Dim rtn     As String

    rtn = Source.Value

    For Each c In Substitute.Cells
        rtn = Application.WorksheetFunction.Substitute(rtn, c.Value, "")
    Next c

    SUBSTITUTEARRAY = rtn

End Function

Which you would call with: =SUBSTITUTEARRAY(A1, F1:F5) where A is the source cell that you want to check and F1:F5 contains the values you want to check for ("Administrator", "Authenticated User" etc...)

It will return TRUE or FALSE depending on whether or not there are other values then those in the cell.

Upvotes: 0

user2140261
user2140261

Reputation: 7993

Add as many names as you want to be highlighted if the cell contains, just use text contains:

enter image description here

enter image description here

Upvotes: 1

Related Questions