hightlight row(s) based on value

[Trevor3007]

Hi,

Does any have the 'VB knowhow' to highlight a row(s) that contain a certain value?

The range is c2:c200 & if it contains any of the following keywords:-

base test completed OK - light green\bold

base test completed failure - Light red\bold

base test on test - light yellow\bold

thank you 'kind person' & i look forward to your reply.

KR
Trevor 3007

 

[James006]

Hi,

What exactly do you mean by ... 'VB knowhow' ... ?

Do you mean VBA ...??? and you are looking for a macro ...

or

Do you mean you need a Conditional Formatting rule ...???

 

[Trevor3007]

Hi,

What exactly do you mean by ... 'VB knowhow' ... ?

Do you mean VBA ...??? and you are looking for a macro ...

or

Do you mean you need a Conditional Formatting rule ...???

good evening 006,

thanks for your reply & yes I do mean VBA & not Conditional Formatting rule sorry for any misleading.

MTIA
Trevor3007

 

[mumps]

Try:

Sub ColorRows()
    Application.ScreenUpdating = False
    Dim rng As Range
    For Each rng In Range("C2:C200")
        Select Case rng.Value
            Case "base test completed OK"
                With rng
                    .EntireRow.Interior.ColorIndex = 4
                    .EntireRow.Font.Bold = True
                End With
            Case "base test completed failure"
                With rng
                    .EntireRow.Interior.ColorIndex = 3
                    .EntireRow.Font.Bold = True
                End With
            Case "base test on test"
                With rng
                    .EntireRow.Interior.ColorIndex = 6
                    .EntireRow.Font.Bold = True
                End With
        End Select
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[Trevor3007]

Try:

Sub ColorRows()
    Application.ScreenUpdating = False
    Dim rng As Range
    For Each rng In Range("C2:C200")
        Select Case rng.Value
            Case "base test completed OK"
                With rng
                    .EntireRow.Interior.ColorIndex = 4
                    .EntireRow.Font.Bold = True
                End With
            Case "base test completed failure"
                With rng
                    .EntireRow.Interior.ColorIndex = 3
                    .EntireRow.Font.Bold = True
                End With
            Case "base test on test"
                With rng
                    .EntireRow.Interior.ColorIndex = 6
                    .EntireRow.Font.Bold = True
                End With
        End Select
    Next rng
    Application.ScreenUpdating = True
End Sub

hi mumps,

thanks for getting back to me. It works , but a few things which I hope you can sort?

(1) rather than fill the whole row when it detects the value in c2:c200, it just fills from a2:x200

(2) I remove the value from within the range c2:c:200, the applicable row will return to 'no fill'

(3) Can you change it so its based a on a cell ref ? As I will be using your 'magic' on various workbooks & it will be easier change . I would be needing 3 ATM to cover the values that are currently set.

Thank you very much...
KR
Trevor 3007

 

[Rick Rothstein]

I might have simpler code for you depending on your answers to these two questions...

1) Are the values in range C2:C200 constants or are they the result of formulas in those cells?

2) Do any values in range C2:C200 start with the words "base test" besides the three phrases you posted in Message #1 ?

 

[Trevor3007]

good afternoon double R

Are the values in range C2:C200 constants or are they the result of formulas in those cells?
No.

Do any values in range C2:C200 start with the words "base test" besides the three phrases you posted in Message #1 ?

not at present, but could change in the future, hence why i suggested specific cell references . I if it could be used on a 'list' like data validation that would good?

I must say your are all ways up for a VBA challenge!

MTIA
Trevor3007

 

[Trevor3007]

Good afternoon double R

Many thanks for your help .

1) Are the values in range C2:C200 constants or are they the result of formulas in those cells?

No ..freehand\cut n paste ....etc

2) Do any values in range C2:C200 start with the words "base test" besides the three phrases you posted in Message #1 ?

not at present, but likely to either change and or add. Could 'data validation' be used? Apologees if you have already considered this.

You certainly are up for a VBA challenge!!

MTIA
Trevor3007

 

[Rick Rothstein]

good afternoon double R

Are the values in range C2:C200 constants or are they the result of formulas in those cells?
No.

Do any values in range C2:C200 start with the words "base test" besides the three phrases you posted in Message
#1 ?

not at present, but could change in the future

This code did not turn out to be "simpler" lengthwise, but it did eliminate the need for a loop...

[table="width: 500"]
[tr]
	[td]Sub ColorRows()
  With Range("C1", Cells(Rows.Count, "C").End(xlUp))
    .Replace "base test completed OK", "=base test completed OK", , , False, , False, False
    With Intersect(.SpecialCells(xlFormulas).EntireRow, Columns("A:X"))
      .Interior.ColorIndex = 4
      .Font.Bold = True
    End With
    Columns("C").Replace "=", "", xlPart
    .Replace "base test completed failure", "=base test completed failure", , , False, , False, False
    With Intersect(.SpecialCells(xlFormulas).EntireRow, Columns("A:X"))
      .Interior.ColorIndex = 3
      .Font.Bold = True
    End With
    Columns("C").Replace "=", "", xlPart
    .Replace "base test on test", "=base test on test", , , False, , False, False
    With Intersect(.SpecialCells(xlFormulas).EntireRow, Columns("A:X"))
      .Interior.ColorIndex = 6
      .Font.Bold = True
    End With
    Columns("C").Replace "=", "", xlPart
  End With
End Sub[/td]
[/tr]
[/table]

 

[mumps]

Try:

Sub ColorRows()
    Application.ScreenUpdating = False
    Dim rng As Range
    For Each rng In Range("C2:C200")
        Select Case rng.Value
            Case "base test completed OK"
                With Range("A" & target.Row).Resize(1, 24)
                    .Interior.ColorIndex = 4
                    .Font.Bold = True
                End With
            Case "base test completed failure"
                With Range("A" & target.Row).Resize(1, 24)
                    .Interior.ColorIndex = 3
                    .Font.Bold = True
                End With
            Case "base test on test"
                With Range("A" & target.Row).Resize(1, 24)
                    .Interior.ColorIndex = 6
                    .Font.Bold = True
                End With
            Case Else
                With Range("A" & target.Row).Resize(1, 24)
                    .Interior.ColorIndex = xlNone
                    .Font.Bold = False
                End With
        End Select
    Next rng
    Application.ScreenUpdating = True
End Sub

Can you explain in more detail by referring to specific cells, rows and columns what you mean by:

Can you change it so its based a on a cell ref ? As I will be using your 'magic' on various workbooks & it will be easier change . I would be needing 3 ATM to cover the values that are currently set.