Why does 2nd cond. format surpass range

[BustedAvi]

Hello,

I have the following code:

On Error Resume Next
    xpH = Application.WorksheetFunction.Match("pH", Range("A1:A40"), 0)
    
    Dim rngToFormat11 As Range
   Set rngToFormat11 = ActiveSheet.Rows(xpH).EntireRow
'first, clear any old Cond format for these cells
   rngToFormat11.FormatConditions.Delete

'add cond 1
   rngToFormat11.FormatConditions.Add Type:=xlCellValue, _
       Operator:=xlGreater, Formula1:="=8.5"
   rngToFormat11.FormatConditions(1).Interior.Color = 65535

'add cond 2
   
   rngToFormat11.FormatConditions.Add Type:=xlCellValue, _
       Operator:=xlLess, Formula1:="=6.5”
   rngToFormat11.FormatConditions(2).Interior.Color = 65535

It scans the range (A1:A40) for keyword "pH", then applies cond. formatting to the entirerow.

When I only use 1 condition, the range selected ends at the last column of entered data. However, when I use both conditions, the formatting is applied to the entire row.

I've tried swapping .EntireRow for .End(xlright) but in this case the formatting is not applied whatsoever.

Any help would be greatly appreciated.

 

[austin350s10]

you need something like

For Each c in rng
   If c = "pH" Then
     'your code here'
   End If
Next c

 

[austin350s10]

If you are more specific about what values you are trying to apply the condition to, I could help you more.

Looks like the code you have doesn't reference a value so it is being applied to any row that contains "Ph" in the "A" column.

 

[JoeMo]

Maybe this will help (untested):

Dim lCol As Long, xpH As Long
xpH = Application.WorksheetFunction.Match("pH", Range("A1:A40"), 0)
lCol = Cells(xpH, Columns.Count).End(xlToLeft).Column
Set rngToFormat11 = ActiveSheet.Range(Cells(xpH, 1), Cells(xpH, lCol))
'rest of code

 

[BustedAvi]

The reason I have to code it like this is because I'm not sure where the values will be. I have a sheet with multiple rows that must undergo their own conditional formatting similar to what is being done here. The trick is that I don't know where the rows and their values will end up because code proceeding this can modify/delete rows of empty data.

Your code seems to work good! Now to apply it to multiple other rows and see whats happens.

Thanks very much.