Highlight entire row based on text in specific cell

[sameeruddin1001]

I have my data in excel from A11 : R1021 of sheet1, if I give a six or seven digit number (EX: 123456) in cell B1 and press ENTER KEY the Excel should automatically find that number from the data range mentioned above (i.e. A11 : R1021) and highlight the entire row (for ex: if the number is located in cell D99, then excel should automatically highlight the entire row from A99 : R99)

Please help me with a VB macro for this.

 

[mohadin]

Hi
Try
put this code in Sheet1 code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
    Dim rg As Range
    If Not Intersect(Target, Range("B1")) Is Nothing Then
        Set Rng = Range("A11").CurrentRegion
        Rng.Interior.Color = xlNone
        For Each rg In Rng
            If rg.Value = Range("B1").Value Then
                Range("A" & rg.Row & ":R" & rg.Row).Interior.Color = vbRed
            End If
        Next
    End If
End Sub

 

[sameeruddin1001]

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

11​

Date

Description of Goods/Services

Ref No.

OCI Number

DC

DC Date

HSN/SAC Code

(PCS)

(per unit)

Total

Discount

Taxable value

Rate

Amt

Rate

Amt

Rate

Amt

12​

01-02-20​

wood

501​

OCI/048105/29

ab/123

01-02-20​

90010000​

4​

100​

400​

100​

300​

5​

15​

5​

10​

5​

10​

13​

02-02-20​

glass

502​

OCI/046803/29

bc/234

02-02-20​

90010000​

3​

200​

600​

100​

500​

5​

15​

5​

10​

5​

10​

14​

03-02-20​

alluminium

503​

OCI/107474/29

ab/456

03-02-20​

90010000​

1​

1200​

1200​

100​

1100​

5​

15​

5​

10​

5​

10​

15​

04-02-20​

steel

504​

OCI/111407/29

bc/567

04-02-20​

90010000​

2​

300​

600​

100​

500​

5​

15​

5​

10​

5​

10​

16​

05-02-20​

iron

505​

OCI/120460/29

ab/302

05-02-20​

90010000​

4​

1100​

4400​

100​

4300​

5​

15​

5​

10​

5​

10​

for example in the above sample data I'm searching for 107474 (which is in cell D14) I'm entering the search criteria (107474) in cell A1 of the same sheet and press ENTER KEY, I want a VBA script to search the data in the given range (Say A12:R1000), locate the number 107474 from D14 cell and highlight the entire row from A14:R:14 for me, is it possible ?

Please help me with a script on this

 

[sameeruddin1001]

Hi
Try
put this code in Sheet1 code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
    Dim rg As Range
    If Not Intersect(Target, Range("B1")) Is Nothing Then
        Set Rng = Range("A11").CurrentRegion
        Rng.Interior.Color = xlNone
        For Each rg In Rng
            If rg.Value = Range("B1").Value Then
                Range("A" & rg.Row & ":R" & rg.Row).Interior.Color = vbRed
            End If
        Next
    End If
End Sub

This code is not working for

 

[mohadin]

(EX: 123456) in cell B1 and press ENTER KEY the Excel should

Then
I'm entering the search criteria (107474) in cell A1 of the same sheet and press ENTER
So first you said B1 so the code is for B1
Then as I can see your post#3 the searching is only in column D is this right? or the code should look every where?
Please give a crystal clear idea
Thanks

 

[mohadin]

Any way
Try
Type you No in A1

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
    Dim rg As Range
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        Set Rng = Range("A11").CurrentRegion.Offset(1)
        Rng.Interior.Color = xlNone
        For Each rg In Rng
            If UBound(Split(rg, "/")) = 2 Then
                If Split(rg, "/")(1) * 1 = Range("B1").Value Then
                    Range("A" & rg.Row & ":R" & rg.Row).Interior.Color = vbRed
                End If
            End If
        Next
    End If
End Sub

 

[sameeruddin1001]

Then
I'm entering the search criteria (107474) in cell A1 of the same sheet and press ENTER
So first you said B1 so the code is for B1
Then as I can see your post#3 the searching is only in column D is this right? or the code should look every where?
Please give a crystal clear idea
Thanks

Your Question: Then as I can see your post#3 the searching is only in column D is this right? or the code should look every where?
My Answer: Yes, Excel should search for the number in column D only and highlight the entire row where the number is found

 

[mohadin]

What about

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
    Dim rg As Range
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        Set Rng = Range("A11").CurrentRegion.Offset(1, 4).Resize(, 1)
        Range("A11").CurrentRegion.Offset(1).Interior.Color = xlNone
        For Each rg In Rng
            If UBound(Split(rg, "/")) = 2 Then
                If Split(rg, "/")(1) * 1 = Target.Value Then
                    Range("A" & rg.Row & ":R" & rg.Row).Interior.Color = vbRed
                End If
            End If
        Next
    End If
End Sub

Updated

 

[Joe4]

Any way
Try
Type you No in A1

The OP repeatedly said that they are entering the value to look for in cell B1, not A1.

So why not simply change your code to look at B1, not A1?
It is not hard. Just change this line:

    If Not Intersect(Target, Range("A1")) Is Nothing Then

to this:

    If Not Intersect(Target, Range("B1")) Is Nothing Then

 

[mohadin]

Have you seen Post#3 bottom linws?

The OP repeatedly said that they are entering the value to look for in cell B1, not A1.

for example in the above sample data I'm searching for 107474 (which is in cell D14) I'm entering the search criteria (107474) in cell A1 of the same sheet and press ENTER KEY, I want a VBA script to search the data in the given range (Say A12:R1000), locate the number 107474 from D14 cell and highlight the entire row from A14:R:14 for me, is it possible ?