highlight row(s) based on value and the highlight row

[Trevor3007]

hi,

I tried conditional formatting but was not suitable (user complained).

can anyone supply me with the VB so if a a cell in row B, contains the word 'not open' it will highlight the row in yellow ?

the range of the worksheet is from a3:s9000...

many thanks in advance.

KR
Trevor3007

 

[DanteAmor]

Execute the following code to put the rows in yellow.

Sub test26()
    Set r = Range("B:B")
    Set b = r.Find("not open", LookAt:=xlWhole, LookIn:=xlValues)
    If Not b Is Nothing Then
        cell = b.Address
        Do
            Range("A" & b.Row & ":S" & b.Row).Interior.ColorIndex = 6
            Set b = r.FindNext(b)
        Loop While Not b Is Nothing And b.Address <> cell
    End If
End Sub

If you want it to be automatic, tell me what you have in column B, you have the text "not open" or it is the result of a formula.
If in column B you have a formula, then put the formula.

 

[Trevor3007]

Execute the following code to put the rows in yellow.

Sub test26()
    Set r = Range("B:B")
    Set b = r.Find("not open", LookAt:=xlWhole, LookIn:=xlValues)
    If Not b Is Nothing Then
        cell = b.Address
        Do
            Range("A" & b.Row & ":S" & b.Row).Interior.ColorIndex = 6
            Set b = r.FindNext(b)
        Loop While Not b Is Nothing And b.Address <> cell
    End If
End Sub

If you want it to be automatic, tell me what you have in column B, you have the text "not open" or it is the result of a formula.
If in column B you have a formula, then put the formula.

Hi DanteAmor,
thank you ...yes it has a vlookup
=IF(ISERROR(VLOOKUP(A16,'Matched Assets'!$A$6:$Q$9000,17,FALSE)),"",VLOOKUP(A16,'not open'!$A$6:$Q$9000,17,FALSE))

also, noticed that if you blank the previous cell that contained 'not open' if leaves the row yellow? And yes automatic.

many thanks again.
KR
Trevor

 

[DanteAmor]

Hi DanteAmor,
thank you ...yes it has a vlookup
=IF(ISERROR(VLOOKUP(A16,'Matched Assets'!$A$6:$Q$9000,17,FALSE)),"",VLOOKUP(A16,'not open'!$A$6:$Q$9000,17,FALSE))

also, noticed that if you blank the previous cell that contained 'not open' if leaves the row yellow? And yes automatic.

many thanks again.
KR
Trevor

So, if you update cell A16, the value in cell B16 can change to "not open"?


Or any other cell influences the result of the formula of B16?

 

[Trevor3007]

So, if you update cell A16, the value in cell B16 can change to "not open"?


Or any other cell influences the result of the formula of B16?

hello & thanks again Dante Amor,

if i put not open in any of the cells in from B3:B900, your code

if i manually put in 'not open' into the range b3:b9000 your code works, if i use the vlookup, your code does not work.
again if i 'blank' the any cell that previously contained 'not open' , it deos not remove the highlighted 'yellow' from the particualr row(s).

I do hope you can sort :}

KR
Trevor

 

[DanteAmor]

Again.
Do you want it to work automatically or do you want to run the macro with a button?

 

[Trevor3007]

hidehi :]

Auto please...my bad, should of stated in previous. :{

MT & very much appreciated Dante :}

 

[DanteAmor]

hidehi :]

Auto please...my bad, should of stated in previous. :{

MT & very much appreciated Dante :}

Point number 1, you want it in automatic.
Point number 2, in cell B16 you have this formula:


=IF(ISERROR(VLOOKUP(A16,'Matched Assets'!$A$6:$Q$9000,17,FALSE)),"",VLOOKUP(A16,'not open'!$A$6:$Q$9000,17,FALSE))

So for a macro to be automatic, I need to know which cells influence the result of cell B16.
According to your formula cell A16 influences the result of the formula of B16.
Question: Is there any other cell that influences the result of cell B16?

In A16 you capture a data or in A16 do you have another formula?

 

[Trevor3007]

Hello DA,
Sorry for not getting back to you sooner .

A16 is just the 'trigger' cell , it does NOT conatin any formula, however there is VB to force certain cells to UPPER / Proper / lower case ...but thats all.

The range for these 'trigger' cells is a3:a9000...

Should 'not open' appear in any of the retrospective cells(b3:b9000), then I need the row(s) to highlight yellow.

I hope this will make sense ?

Kind regards
Trevor3007

 

[DanteAmor]

Put the following code in your sheet events

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        If Target.Count > 1000 Then Exit Sub
        For Each c In Target
            If LCase(c.Offset(0, 1).Value) = LCase("not open") Then
                Range("A" & c.Row & ":S" & c.Row).Interior.ColorIndex = 6
            Else
                Range("A" & c.Row & ":S" & c.Row).Interior.ColorIndex = xlNone
            End If
        Next
    End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.