VBA Find Function

[Whylucky]

So I am trying to code a macro that will search through a selected range of cells for key letters, for instance this cell may contain any combination of B, C, Te, Tc, RH, or LH. I would preferably like to search with capitalization being a factor but it is not a deal breaker. Below is a sample of what i have if the cell has a B, C it works for B but ignores the C i need it t o recognize both, any help or suggestions?

 If InStr(1, ActiveCell.Text, "B") Then        Range("O" + CStr(ActiveCell.Row)).Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End If
        
    If InStr(ActiveCell.Text, "C") Then
        Range("P" + CStr(ActiveCell.Row)).Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End If

 

[Norie]

When you find B you change the active cell, so when the code is looking for C it isn't looking in the same cell as it was for C.

Try this.

 If InStr(1, ActiveCell.Text, "B") Then 
        With Range("O" & ActiveCell.Row).Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End If
        
    If InStr(ActiveCell.Text, "C") Then
        With Range("P" & ActiveCell.Row)).Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End If

 

[bewsh1987]

I have taken the end ifs away as you have already closed the if statements maybe this is where you was going wrong

If InStr(1, ActiveCell.Text, "B") Then        Range("O" + CStr(ActiveCell.Row)).Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With

        
    If InStr(ActiveCell.Text, "C") Then Range("P" + CStr(ActiveCell.Row)).Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With

 

[bewsh1987]

go with Norie seem to work better

 

[Whylucky]

yea i just realized that after i posted, dumbo move

 

[Whylucky]

Is there anyway that you know of, once the cells have been highlighted to find the maximum of them? i know this could be a new thread but it is a direct relation to the above code

 

[Norie]

The maximum of what exactly?

 

[Whylucky]

so the code you helped fix earlier (thank you by the way) is going through and highlighting cells on a case by case basis, i then need to find maximum of the highlighted cells (because that for certain case)

 

[Norie]

Perhaps something like this.

    If InStr(1, ActiveCell.Text, "B") Then 
        With Range("O" & ActiveCell.Row).Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
            
        End With

        If MaxValue = 0 Or Range("O" & ActiveCell.Row)>MaxValue Then
            MaxValue = Range("O" & ActiveCell.Row).Value
        End If

    End If
        
    If InStr(ActiveCell.Text, "C") Then
        With Range("P" & ActiveCell.Row)).Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With

        If MaxValue = 0 Or Range("P" & ActiveCell.Row)>MaxValue Then
            MaxValue = Range("P" & ActiveCell.Row).Value
        End If

    End If

 

[Whylucky]

i created a new thread with more information, i dont think ur method would work because it is in a counter, here is the link

http://www.mrexcel.com/forum/excel-questions/748604-max-highlighted-cells.html#post3678247