Results 1 to 7 of 7

Thread: [RESOLVED] excel 2000 worsheet macro

  1. #1

    Thread Starter
    Addicted Member habenero's Avatar
    Join Date
    Aug 2015
    Posts
    224

    Resolved [RESOLVED] excel 2000 worsheet macro

    If someone can give me a nudge and then point me to the way to this solve this task-

    I have a range on numbers in a worksheet whose starting row will change as more data is added; The range might also change in size but always 6 columns contiguously (I.E. B3;G8)

    I have another range single row (6 columns wide) and starting row will change as more data is added (I.E B10;G10)

    Now I want to test the larger range against the smaller range for equal values and then upon a true condition - change the background color of that individual cell in that larger range

    I know it can be done with a macro inside the workbook and that's fine with me as this is a local task.
    Last edited by habenero; Jan 4th, 2017 at 10:51 AM. Reason: clarity

  2. #2
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: excel 2000 worsheet macro

    Quote Originally Posted by habenero View Post
    If someone can give me a nudge and then point me to the way to this solve this task-
    We can try...

    I have a range on numbers in a worksheet whose starting row will change as more data is added; The range might also change in size but always 6 columns contiguously (I.E. B3;G8)

    I have another range single row (6 columns wide) and starting row will change as more data is added (I.E B10;G10)
    um? Why would your starting row change?

    Now I want to test the larger range against the smaller range for equal values and then upon a true condition - change the background color of that individual cell in that larger range
    Comparing what?
    - each cell in its corresponding column?
    - each value in the smaller exists somewhere in the larger?
    - that each combination of 6 cells has the same numbers...?

    Do you mean two separate ranges on the same sheet with the same columns?


    Have a look at Vlookup (excel function) to see if that does what you need.
    If not, then you'll need to post your code and which bits don't work right - perhaps a pseudo code of what you want and your code that you tried...?

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  3. #3

    Thread Starter
    Addicted Member habenero's Avatar
    Join Date
    Aug 2015
    Posts
    224

    Re: excel 2000 worsheet macro

    Quote Originally Posted by Ecniv View Post
    We can try...

    um? Why would your starting row change?

    I can be flexible and put that data in separate sheets instead of the same worksheet. Every day those numbers change and new data is added but I can put new data on a new worksheet

    Comparing what?
    - each cell in its corresponding column? numbers would never be the same in either range

    - each value in the smaller exists somewhere in the larger? that's the conditional part of the code - I can record a macro and go from there but I'm doing the conditional test

    - that each combination of 6 cells has the same numbers...? no

    Comparing cell "contents" in other words does Cell x = cell y

    I stink at making looping programs and I know I would need one here to go through the 6 cell range versus the larger range

    Do you mean two separate ranges on the same sheet with the same columns?
    yes - if I use the same wksht


    Have a look at Vlookup (excel function) to see if that does what you need.

    If not, then you'll need to post your code and which bits don't work right - perhaps a pseudo code of what you want and your code that you tried...?
    Will lookup the Vlookup function and see what that does for me

    To further explain - this has to do with lottery numbers - my own vs what are the "winning" numbers. The larger range are my numbers and then in the 6 column range are the "winning numbers". Then I want to conditionally color the background of those matching cells in the larger range with the drawn/winning numbers

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: excel 2000 worsheet macro

    whose starting row will change
    how can the starting row be determined?
    The range might also change in size
    how to determine the number of rows?
    and starting row will change
    same question, criteria to match this row?

    maybe post a sample workbook, zip first
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Addicted Member habenero's Avatar
    Join Date
    Aug 2015
    Posts
    224

    Re: excel 2000 worsheet macro

    will stick my foot in the water and give you some real bad code (to come 1/2 way)
    Presently bone-ing up on the Vlookup function

    It would be comparing a larger range (6 columns wide - all different values) starting row can be fixed if I use multiple worksheets

    Comparing this range (individually) with values of a range (also 6 columns wide (1 row only)

    If the values match then a cell background color is used like in a keno machine

    I've searched all over this site as this kind of routine cannot be that unique or rare. In a way I would be comparing values of an array with a smaller array in excel

  6. #6

    Thread Starter
    Addicted Member habenero's Avatar
    Join Date
    Aug 2015
    Posts
    224

    Re: excel 2000 worsheet macro

    Quote Originally Posted by habenero View Post
    will stick my foot in the water and give you some real bad code (to come 1/2 way)
    Presently bone-ing up on the Vlookup function

    It would be comparing a larger range (6 columns wide - all different values) starting row can be fixed if I use multiple worksheets

    Comparing this range (individually) with values of a range (also 6 columns wide (1 row only)

    If the values match then a cell background color is used like in a keno machine

    I've searched all over this site as this kind of routine cannot be that unique or rare. In a way I would be comparing values of an array with a smaller array in excel
    I'm going to go in the way of conditional formatting

    Thanks to all for your help

  7. #7

    Thread Starter
    Addicted Member habenero's Avatar
    Join Date
    Aug 2015
    Posts
    224

    Re: excel 2000 worsheet macro

    Quote Originally Posted by habenero View Post
    I'm going to go in the way of conditional formatting

    Thanks to all for your help
    For all that care - this suits me fine

    Sub Color_tester()
    Set myRange = Range("A3:F8")
    For Each cell In myRange
    If cell.Value = Range("A10") Then
    cell.Interior.ColorIndex = 4
    ElseIf cell.Value = Range("B10") Then
    cell.Interior.ColorIndex = 4
    ElseIf cell.Value = Range("C10") Then
    cell.Interior.ColorIndex = 4
    ElseIf cell.Value = Range("D10") Then
    cell.Interior.ColorIndex = 4
    ElseIf cell.Value = Range("E10") Then
    cell.Interior.ColorIndex = 4
    ElseIf cell.Value = Range("F10") Then
    cell.Interior.ColorIndex = 4
    End If
    Next
    End Sub

Posting Permissions

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



Click Here to Expand Forum to Full Width