Highlighting row based on text

Sean Lim · Feb 11, 2022

I am trying to highlight the row in red colour from B to AS based on the word 'QUARANTINE' and unhighlight if the word is 'SERVICEABLE'.
Thank you!

TESTING.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

AT

AU

AV

AW

AX

AY

1

PLANT: 2896

2

3

STILL WITH REPAIR CENTRE

AOG OUTBOUND

QUARANTINE PARTS

RESERVED

NON CARESING POOL ITEMS

FOR SCRAP

4

07/29/2013 & 08/07/2013 & 08/12/2013 & 08/14/2013 & 08/15/2013 & 08/16/2013 & 08/17/2013 & 08/19/2013

5

UPS REF#

STOCK TO DOCK DATE

STOCK TO SAP DATE

STOCK TO INV DATE

PART NO

DESCRIPTIONS

SERIAL NOS.

Platform

INVENTORY TYPE

STATUS

FAA

CAA

EASA

CAAS

CAAC

JCAB

DCAM

CERTIFICATE OF CONFORMANCE

BILATERAL AGREEMENT

OTHERS

IMPORT CUSTOM VALUE (USD)

EXPORT CUSTOM VALUE (USD)

RECEIVED FROM

INBOUND VIA

INBOUND FLIGHT DETAILS

INBOUND REFERENCES

WEIGHT (Lbs)

WEIGHT (KG)

DIMENSION (INCHES) LTH WTH HT

DIMENSION (CM) LTH WTH HT

QTY IN

QTY OUT

QTY BAL

DATE CARE IS NOTIFIED

DATE SHIPPED

POD DATE / STATUS

OUTBOUND TO

STATUS

OUTBOUND VIA

OUTBOUND FLIGHT DETAILS

OUTBOUND REFERENCES

LOCATION

IMPORT PERMIT

Outbound Permit

SAP (02 July 2020)

Remarks

CC STATUS

6

NA

18-Dec-06

1779MK2

VALVE SCAVENGE NO

17792124

V2500

SERVICEABLE

REPAIRED

YES

NA

REPORT

USD 7,205.00

GOODRICH UK

DHL

9908457451

P/O: 3438267/20023135EG8, REF: RT38860001

4.4

2

11

28

1

0

1

A5BS17B04

7

NA

3-Jan-07

808603-1

DUCT, REHEATER OUTLET

NA

B777-Non Serialized

SERVICEABLE

NEW

YES

NA

NO REPORT

USD 154.10

HS US

DHL

9219052721

P/O: ANZ-028, REF: 2471869TL1

2.2

1

11

27

28

1

0

1

A5BS11D04

8

NA

8-Jan-07

11388-2

SENSOR ASSY COOLING AIRFLOW

1357

B777

QUARANTINE

NEW

YES

NA

NO REPORT

USD 1,414.85

HS US

DHL

9219053141

P/O: ANZ-043, REF: 2471919TL

2.2

1

11

27

1

0

1

A5BS11E04

9

NA

26-Jan-07

810503-8

CONTROLLER, EAI VALVE

2017

B777

SERVICEABLE

NEW

YES

NA

NO REPORT

USD 1,719.61

HS US

DHL

9225280105

P/O: ANZ-012, REF: 2471718TL

4.4

2

9

22

23

1

0

1

A5BS11E04

10

NA

2-May-07

73984300

OIL TEMPERATURE, THERMOCOUPLE

3700

V2500

SERVICEABLE

INSPECTED

YES

NA

YES

NA

WORK ORDER FORM

USD 250.00

GRAND PRAIRIE

DHL

8151222892

P/O: 3709833, REF: 51369

2.2

1

9

23

1

0

1

A5BS20A04

11

NA

21-May-07

810247-1

DUCT, CONDENSER REHEATER IN

NA

B777-Non Serialized

SERVICEABLE

NEW

YES

NA

NO REPORT

USD 774.99

HS US

DHL

7559940032

P/O: ANZ-041, REF: 2471912TL

11.0

5

30

20

77

50

1

0

18-Jun-20

HS WINDSOR USA

NORMAL

FEDEX

GP#118173-813647797546

P/O: 403147952/10

12

13

14

15

16

HSCARE MASTER LIST 2011-CUR

Cell Formulas
Range	Formula
AF6:AH11	AF6	=AC6*2.54
AL6:AL10	AL6	=IF($E6>1,(IF($AK6=0,IF(AL6="",TODAY(),AL6),"")),"")
AM6:AM10	AM6	=AL6
AB6:AB11	AB6	=AA6/2.2
AK6:AK11	AK6	=AI6-AJ6
AN6:AN11	AN6	=AL6
H12	H12	=IFNA((VLOOKUP(E12,E:H,4,FALSE)),"")

number kruncher · Feb 11, 2022

Use conditional Formatting..

And change the Applies to, to match your Range of Rows.

The Formula $I6 relates to the Top Row of the Range, and only the column is locked.

HTH

Sean Lim · Feb 11, 2022

number kruncher said:
Use conditional Formatting..

And change the Applies to, to match your Range of Rows.

View attachment 57544

The Formula $I6 relates to the Top Row of the Range, and only the column is locked.

HTH

I have tried before, but this list keeps growing, everyday there are new data added into it. Sometimes I have to insert or delete row.
So conditional formatting does not work. I am looking for VBA help.
However, thank you for your time!

mohadin · Feb 11, 2022

Hi
Try
In sheet module

VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Columns("I")) Is Nothing Then
        If Target.Value = UCase("Quarantine") Then
            Rows(Target.Row).EntireRow.Interior.Color = vbRed
            Else
            Rows(Target.Row).EntireRow.Interior.Color = vbWhite
        End If
    End If
End Sub

mohadin · Feb 11, 2022

Sorry
Try this

VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Columns("I")) Is Nothing Then
        If Target.Value = UCase("Quarantine") Then
          Range(Cells(Target.Row, 2), Cells(Target.Row, 41)).Interior.Color = vbRed
            Else
            Rows(Target.Row).EntireRow.Interior.Color = vbWhite
        End If
    End If
End Sub

Sean Lim · Feb 11, 2022

mohadin said:

Sorry
Try this

VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Columns("I")) Is Nothing Then
        If Target.Value = UCase("Quarantine") Then
          Range(Cells(Target.Row, 2), Cells(Target.Row, 41)).Interior.Color = vbRed
            Else
            Rows(Target.Row).EntireRow.Interior.Color = vbWhite
        End If
    End If
End Sub

Hi Mohadin, it works like magic!
But I am sorry to trouble you. Instead of vbWhite, can it be change to no colour fill instead?
Also, can I add a few other highlighting conditions?

If Column "I" has the word "RESERVED", highlight Range(Cells(Target.Row, 2), Cells(Target.Row, 41)).Interior.Color = RGB(204,102,255)
If Column "AP" has the word "AOG", highlight Range(Cells(Target.Row, 2), Cells(Target.Row, 41)).Interior.Color = RGB(255,204,153)

Thank you!

mohadin · Feb 12, 2022

Hi
Try

VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Union(Columns("I"), Columns("AP"))) Is Nothing Then
        Select Case Target.Value
        Case UCase("Quarantine")
             Range(Cells(Target.Row, 2), Cells(Target.Row, 41)).Interior.Color = vbRed
        Case UCase("RESERVED")
             Range(Cells(Target.Row, 2), Cells(Target.Row, 41)).Interior.Color = RGB(204, 102, 255)
        Case UCase("AOG")
             Range(Cells(Target.Row, 2), Cells(Target.Row, 41)).Interior.Color = RGB(255, 204, 153)
            Case Else
              Range(Cells(Target.Row, 2), Cells(Target.Row, 41)).Interior.Color = RGB(255, 255, 255)
        End Select
    End If
End Sub

Or

VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Union(Columns("I"), Columns("AP"))) Is Nothing Then
     With Range(Cells(Target.Row, 2), Cells(Target.Row, 41))
        Select Case Target.Value
        Case UCase("Quarantine")
            .Interior.Color = vbRed
        Case UCase("RESERVED")
             .Interior.Color = RGB(204, 102, 255)
        Case UCase("AOG")
             .Interior.Color = RGB(255, 204, 153)
            Case Else
              .Interior.Color = RGB(255, 255, 255)
        End Select
         End With
    End If
End Sub

Sean Lim · Feb 14, 2022

mohadin said:

Hi
Try

VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Union(Columns("I"), Columns("AP"))) Is Nothing Then
        Select Case Target.Value
        Case UCase("Quarantine")
             Range(Cells(Target.Row, 2), Cells(Target.Row, 41)).Interior.Color = vbRed
        Case UCase("RESERVED")
             Range(Cells(Target.Row, 2), Cells(Target.Row, 41)).Interior.Color = RGB(204, 102, 255)
        Case UCase("AOG")
             Range(Cells(Target.Row, 2), Cells(Target.Row, 41)).Interior.Color = RGB(255, 204, 153)
            Case Else
              Range(Cells(Target.Row, 2), Cells(Target.Row, 41)).Interior.Color = RGB(255, 255, 255)
        End Select
    End If
End Sub

Or

VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Union(Columns("I"), Columns("AP"))) Is Nothing Then
     With Range(Cells(Target.Row, 2), Cells(Target.Row, 41))
        Select Case Target.Value
        Case UCase("Quarantine")
            .Interior.Color = vbRed
        Case UCase("RESERVED")
             .Interior.Color = RGB(204, 102, 255)
        Case UCase("AOG")
             .Interior.Color = RGB(255, 204, 153)
            Case Else
              .Interior.Color = RGB(255, 255, 255)
        End Select
         End With
    End If
End Sub

Hi Mohadin, it works!
Thank you so much!

mohadin · Feb 15, 2022

You are very welcome
And thank you for the feedback
Be happy and safe
Regards

Highlighting row based on text

Sean Lim

New Member

Excel Facts

number kruncher

Active Member

Sean Lim

New Member

mohadin

Well-known Member

mohadin

Well-known Member

Sean Lim

New Member

Attachments

mohadin

Well-known Member

Sean Lim

New Member

mohadin

Well-known Member

Similar threads

Forum statistics

Share this page

Highlighting row based on text

New Member

Excel Facts

Active Member

New Member

Well-known Member

Well-known Member

New Member

Attachments

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock