+ Reply to Thread
Results 1 to 24 of 24

I have value in Range V and Range T how loop this values to avoid this extra two steps.

  1. #1
    Registered User
    Join Date
    08-09-2021
    Location
    Doha
    MS-Off Ver
    365
    Posts
    13

    I have value in Range V and Range T how loop this values to avoid this extra two steps.

    valuea = Range("V6")
    Last = Cells(Rows.Count, 2).End(xlUp).Row
    For i = Last To 1 Step -1
    If (Cells(i, 2).Value) = Range("T6") Then
    Cells(i, "A").EntireRow.Resize(valuea, 11).Select
    Selection.Interior.ColorIndex = 0
    End If
    Next i
    Selection.Activate
    Selection.Interior.ColorIndex = 15


    valueb = Range("V7")
    Last = Cells(Rows.Count, 2).End(xlUp).Row
    For i = Last To 1 Step -1
    If (Cells(i, 2).Value) = Range("T7") Then
    Cells(i, "A").EntireRow.Resize(valueb, 11).Select
    Selection.Interior.ColorIndex = 0
    End If
    Next i
    Selection.Activate
    Selection.Interior.ColorIndex = 15


    valuec = Range("V8")
    Last = Cells(Rows.Count, 2).End(xlUp).Row
    For i = Last To 1 Step -1
    If (Cells(i, 2).Value) = Range("T8") Then
    Cells(i, "A").EntireRow.Resize(valuec, 11).Select
    Selection.Interior.ColorIndex = 0
    End If
    Next i
    Selection.Activate
    Selection.Interior.ColorIndex = 15


    please help me shorten this code.

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: I have value in Range V and Range T how loop this values to avoid this extra two steps

    It looks like you want just the last match for each of the three values to be filled grey - is that correct?
    Rory

  3. #3
    Registered User
    Join Date
    08-09-2021
    Location
    Doha
    MS-Off Ver
    365
    Posts
    13

    Re: I have value in Range V and Range T how loop this values to avoid this extra two steps

    Quote Originally Posted by rorya View Post
    It looks like you want just the last match for each of the three values to be filled in grey - is that correct?
    I have 1 to 10 rows in columns V & T
    need to pick each value from T for finding the values from B and each value from V for the row count for colouring grey.

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: I have value in Range V and Range T how loop this values to avoid this extra two steps

    Your current code appears to clear the interior colour from any matching rows and then only makes the last selection found grey.

  5. #5
    Registered User
    Join Date
    08-09-2021
    Location
    Doha
    MS-Off Ver
    365
    Posts
    13

    Re: I have value in Range V and Range T how loop this values to avoid this extra two steps

    Yes,
    it's like this.

    Attachment 743462Attachment 743462

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: I have value in Range V and Range T how loop this values to avoid this extra two steps

    Those attachments don't work for me.

  7. #7
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: I have value in Range V and Range T how loop this values to avoid this extra two steps

    Untested, but perhaps something like this:

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    08-09-2021
    Location
    Doha
    MS-Off Ver
    365
    Posts
    13

    Re: I have value in Range V and Range T how loop this values to avoid this extra two steps

    Quote Originally Posted by rorya View Post
    Those attachments don't work for me.
    The problem is if there is no values in (V:V) and (T:T) i have to skip this step otherwise it needs to pick the first V6 T6 then one by one to the bottom.


    valuea = Range("V6:V10")
    Last = Cells(Rows.Count, 2).End(xlUp).Row
    For i = Last To 1 Step -1
    If (Cells(i, 2).Value) = Range("T6:T10") Then
    Cells(i, "A").EntireRow.Resize(valuea, 11).Select
    Selection.Interior.ColorIndex = 0
    End If
    Next i
    Selection.Activate
    Selection.Interior.ColorIndex = 15

  9. #9
    Registered User
    Join Date
    08-09-2021
    Location
    Doha
    MS-Off Ver
    365
    Posts
    13

    Re: I have value in Range V and Range T how loop this values to avoid this extra two steps

    Quote Originally Posted by rorya View Post
    Untested, but perhaps something like this:

    Please Login or Register  to view this content.
    Attachment 743466

    here is the issue now.

  10. #10
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: I have value in Range V and Range T how loop this values to avoid this extra two steps

    Again, attachments like that do not work for me. You have to use the manage attachments link and upload them that way.

  11. #11
    Registered User
    Join Date
    08-09-2021
    Location
    Doha
    MS-Off Ver
    365
    Posts
    13

    Re: I have value in Range V and Range T how loop this values to avoid this extra two steps

    Quote Originally Posted by rorya View Post
    Again, attachments like that do not work for me. You have to use the manage attachments link and upload them that way.


    Set theRanges(n) = Cells(i, "A").EntireRow.Resize(theSizes, 11)

    it is stopped in this line.

  12. #12
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: I have value in Range V and Range T how loop this values to avoid this extra two steps

    Typo - change thesizes to thesizes(n, 1)

  13. #13
    Registered User
    Join Date
    08-09-2021
    Location
    Doha
    MS-Off Ver
    365
    Posts
    13

    Re: I have value in Range V and Range T how loop this values to avoid this extra two steps

    Dim theValues
    Dim theRanges(1 To 3) As Range
    Dim theSizes
    theSizes = Range("V6:V8").Value
    theValues = Range("T6:T8").Value

    Last = Cells(Rows.Count, 2).End(xlUp).Row
    For i = Last To 1 Step -1
    For n = 1 To 3
    If (Cells(i, 2).Value) = theValues(n, 1) Then
    Set theRanges(n) = Cells(i, "A").EntireRow.Resize(theSizes(n, 1), 11)
    theRanges(n).Interior.ColorIndex = 0
    Exit For
    End If
    Next n
    Next i
    For n = 1 To 3
    If Not theRanges(n) Is Nothing Then theRanges(n).Interior.ColorIndex = 15
    Next n



    Stopped in the same line

  14. #14
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: I have value in Range V and Range T how loop this values to avoid this extra two steps

    With what error? Are there values in V6:V8?

  15. #15
    Registered User
    Join Date
    08-09-2021
    Location
    Doha
    MS-Off Ver
    365
    Posts
    13

    Re: I have value in Range V and Range T how loop this values to avoid this extra two steps

    Yes,

    A to E columns have this lines

    Lines Article No. Description UOM Qty
    1 00180119 KIVIK FRM SO3 PCS 2
    2 00180119 KIVIK FRM SO3 PCS 2
    3 00223864 SODERHAMN FRM CHAISE LNG PCS 5
    4 00223864 SODERHAMN FRM CHAISE LNG PCS 5
    5 00425857 HYL N PCKT SPR MATT 160X200 FIRM/WHITE PCS 3
    6 20223882 SODERHAMN FRM THREE-SEAT SEC PCS 5
    7 20493440 PARUP frm so2 PCS 2
    8 20493440 PARUP frm so2 PCS 2
    9 20493440 PARUP frm so2 PCS 2
    10 20493440 PARUP frm so2 PCS 2
    11 40272287 MALFORS FOAM MATT 90X200 MEDIUM FIRM/WHITE PCS 13
    12 40272287 MALFORS FOAM MATT 90X200 MEDIUM FIRM/WHITE PCS 13

    S6 to V7 have this values

    CCLU6698333 20493440 2 2
    CCLU6698333 20223882 1 1


    I have to color grey in any 2 rows from 7 to 10 for 20493440 and 1 row for 20223882 line number 6.
    Last edited by jeevan Raj; 08-09-2021 at 01:34 PM.

  16. #16
    Registered User
    Join Date
    08-09-2021
    Location
    Doha
    MS-Off Ver
    365
    Posts
    13

    Re: I have value in Range V and Range T how loop this values to avoid this extra two steps

    Quote Originally Posted by rorya View Post
    With what error? Are there values in V6:V8?
    Run time error 1004
    application-defined or object-defined error.

  17. #17
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: I have value in Range V and Range T how loop this values to avoid this extra two steps

    Nothing in V8?

  18. #18
    Registered User
    Join Date
    08-09-2021
    Location
    Doha
    MS-Off Ver
    365
    Posts
    13

    Re: I have value in Range V and Range T how loop this values to avoid this extra two steps

    Quote Originally Posted by rorya View Post
    Nothing in V8?
    Not now but sometimes values will come to S6 to V17 and the lines will go to more than 300.
    Last edited by jeevan Raj; 08-09-2021 at 01:58 PM.

  19. #19
    Registered User
    Join Date
    08-09-2021
    Location
    Doha
    MS-Off Ver
    365
    Posts
    13

    Re: I have value in Range V and Range T how loop this values to avoid this extra two steps

    hi rorya,

    please check this

    Sub grey()


    Last = Cells(Rows.Count, 2).End(xlUp).Row
    For i = Last To 1 Step -1

    If Cells(i, 2).Value = Range("T6").Value Then

    Cells(i, "A").EntireRow.Resize(Range("V6").Value, 11).Select
    Selection.Interior.ColorIndex = 0

    End If
    Next i

    Selection.Interior.ColorIndex = 15

    Last = Cells(Rows.Count, 2).End(xlUp).Row
    For i = Last To 1 Step -1

    If Cells(i, 2).Value = Range("T7").Value Then

    Cells(i, "A").EntireRow.Resize(Range("V7").Value, 11).Select
    Selection.Interior.ColorIndex = 0

    End If
    Next i

    Selection.Interior.ColorIndex = 15

    Last = Cells(Rows.Count, 2).End(xlUp).Row
    For i = Last To 1 Step -1

    If Cells(i, 2).Value = Range("T8").Value Then

    Cells(i, "A").EntireRow.Resize(Range("V8").Value, 11).Select
    Selection.Interior.ColorIndex = 0




    End If
    Next i

    Selection.Interior.ColorIndex = 15


    End Sub

    first two lines are working with this v8 have no value that's why third line faild.

  20. #20
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: I have value in Range V and Range T how loop this values to avoid this extra two steps

    Try this code
    Please Login or Register  to view this content.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  21. #21
    Registered User
    Join Date
    08-09-2021
    Location
    Doha
    MS-Off Ver
    365
    Posts
    13

    Re: I have value in Range V and Range T how loop this values to avoid this extra two steps

    Quote Originally Posted by kvsrinivasamurthy View Post
    Try this code
    Please Login or Register  to view this content.
    Thanks for your help, its stopped in this line.

    Cells(i, "A").EntireRow.Resize(Ary(J, 2), 11).Select

  22. #22
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: I have value in Range V and Range T how loop this values to avoid this extra two steps

    Pl post a sample file.

  23. #23
    Registered User
    Join Date
    08-09-2021
    Location
    Doha
    MS-Off Ver
    365
    Posts
    13

    Re: I have value in Range V and Range T how loop this values to avoid this extra two steps

    Quote Originally Posted by kvsrinivasamurthy View Post
    Pl post a sample file.
    it's working well but the error is there I can't complete the macro.

    Attachment 743746

    Attachment 743743

    Attachment 743745Attachment 743744

  24. #24
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: I have value in Range V and Range T how loop this values to avoid this extra two steps

    Attachment not opening. Pl see yellow banner at the top.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Loop through range values, calculate, then output to a new range
    By Jackson2806 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-25-2021, 07:54 AM
  2. Replies: 7
    Last Post: 01-31-2019, 01:17 PM
  3. Replies: 1
    Last Post: 11-24-2015, 03:00 AM
  4. Loop through each folder copy values from range in file1 to named range in file2
    By dafella in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-16-2015, 05:19 PM
  5. Replies: 5
    Last Post: 09-04-2012, 09:59 AM
  6. Macro Loop to Extract Specific Values and Min/Max Values From Column/Rows Range
    By ExcelQuestFL in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 12-19-2010, 09:19 AM
  7. [SOLVED] copy exact values from RangeA to Range B which has extra rows
    By guptasa@gossami .com in forum Excel General
    Replies: 1
    Last Post: 05-16-2005, 05:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1