CODEWITHSUNDEEP
excelexcel-formulaformulaconditional-formatting
Amna Hussain
Amna Hussain

Reputation: 45

Highlight cells in a column depending on a letter present in cell range

I have a column indicating progress on projects (in percentages) which should turn red if in the corresponding rows there is no letter P. With the following formula something very strange happens: 

=ISERROR(FIND("p",$H5:$Y65))

So I have set P not being an error and the cell that doesn't contain P should be formatted red. However, with this formula, only if there is a P in the first column i.e. H does it format. The formula seems to ignore all the other columns after H.

Any suggestions?

Upvotes: 1

Views: 167

Answers (2)

pnuts
pnuts

Reputation: 59475

FIND does not have the functionality you seek, it searches within a string not within an array. Try selecting from row 5 to row 65 in the relevant column and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true:: 

=ISERROR(MATCH("P",$H5:$Y5,0))

Format..., select red fill, OK, OK.

Assumes P is entire cell content, not merely part of.

Upvotes: 1

Schalton
Schalton

Reputation: 3104

I'd reconsider your range, you say corresponding rows, was Y65 instead of Y5 a typographical error? If you filldown with your current formula you'll have overlapping cells as the next row will cover H6:Y66 and the range H6:Y65 will have been checked again.

That said, pnuts is correct, but you can achieve this with a user defined function such as: 

Function BooleanRangeFind(SearchRange As Range, MatchCriteria As String, CaseSensative As Boolean) As Boolean
Dim Rng As Range
Dim CurStr As String
'checks and changes the MatchCriteria to Uppercase, this is
'the easiest way to ignore case sensativity
If CaseSensative = False Then MC = UCase(MatchCriteria)
'iterates through each cell in the range and checks for the MatchCriteria
'in each cell
For Each Rng In SearchRange
    'Case Sensativity Check
    If CaseSensative = False Then
        CurStr = UCase(Rng.Value)
    Else
        CurStr = Rng.Value
    End If
    If InStr(1, CurStr, MC) <> 0 Then
    'If the MC is in the Current Range
    'the formula stops looking and returns
    'a true
        BooleanRangeFind = True
        Exit Function
    End If
Next Rng
'Default Value is False
BolleanRangeFind = False
End Function

Where your formula would be either

=BooleanRangeFind($H6:$Y65,"p",False)

or if my assumption is correct:

=BooleanRangeFind($H6:$Y6,"p",False)

Upvotes: 0

Related Questions