+ Reply to Thread
Results 1 to 10 of 10

Highlight Cell with unique number based off VLOOKUP outcome

  1. #1
    Registered User
    Join Date
    08-18-2019
    Location
    Australia
    MS-Off Ver
    MS Office 365
    Posts
    28

    Highlight Cell with unique number based off VLOOKUP outcome

    G'day,

    I've got a doozy for everyone to have a peek at, this might take a bit to explain what I'm after.

    The "Input" sheet is where my raw data comes from a barcode scanner, I copy and paste into this sheet in order to give the "Output" sheet a reference. The data referenced as SP1-SP28 is the module reference per string on the farm (there are 3 strings per row, hence the 1024 - 1, 1024 - 2 and 1024 - 3 as reference). The total of 3 strings in a single row equates to 84 modules (hence the naming convention shown in the "Scanning" sheet eg 01.01.41.01 - 01.01.41.84)
    The "Output" sheet has a VLOOKUP formula to grab the data from the "Input" sheet (it will be pasted in a random order hence the vlookup funtion)
    The "Table" sheet is the conversion from the chronological ordering the barcode requires (it cant function with the numbering convention the farm has so it required a conversion)
    The "Master" sheet is the raw data from the manufacturer that gives us the barcode and the Watt and Current rating for each module.
    The entire solar farm im working on has been mapped in cell form and each cell has been given a unique number convention based on its location on the farm (see "Scanning as reference")

    What I require is the following:
    -The "Output" sheet to find the bacode within the "Master" sheet and highlight the cell within "Output" to be the same colour fill and text as its found cell.
    -A way for the data within "Output" to find its companion cell within "Scanning" and highlight the cell the same.

    The output I'm hoping to achieve is, once i input the raw data into the "Input" sheet, it will automatically find what kind of module it is (current and watt) and highlight the "Scanning" sheet to give a colour coded reference and see if the right module has been installed in its correct location.

    If you require further explaination, let me know and I'll help where I can.

    This is a big one but a game changer for me if I can get it so thank you in advance for even reading this far.

    Shonky
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Highlight Cell with unique number based off VLOOKUP outcome

    It might be nice if you were to acknowledge the help you got in your previous thread... rather than just accepting an answer, and saying nothing..
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Highlight Cell with unique number based off VLOOKUP outcome

    Please add a few expected results to your sample, so we know exactly what you want. I couldn't see any of the values (Master, D2 to D5) in output. So I stopped looking, pending clarification.

  4. #4
    Registered User
    Join Date
    08-18-2019
    Location
    Australia
    MS-Off Ver
    MS Office 365
    Posts
    28

    Re: Highlight Cell with unique number based off VLOOKUP outcome

    Quote Originally Posted by Glenn Kennedy View Post
    It might be nice if you were to acknowledge the help you got in your previous thread... rather than just accepting an answer, and saying nothing..
    You are entirely correct and I do apologise for my lack of response. I'm normally quite good at doing that. I've gone ahead and fixed that up

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Highlight Cell with unique number based off VLOOKUP outcome

    I have a feeling that you want the matches for compnent X to be one colour... for component Y to be nother colour, and so on. Is that the case? If so, you'll probably need VBA. If that IS your requirement, is VBA OK?

  6. #6
    Registered User
    Join Date
    08-18-2019
    Location
    Australia
    MS-Off Ver
    MS Office 365
    Posts
    28

    Re: Highlight Cell with unique number based off VLOOKUP outcome

    Quote Originally Posted by Glenn Kennedy View Post
    I have a feeling that you want the matches for compnent X to be one colour... for component Y to be nother colour, and so on. Is that the case? If so, you'll probably need VBA. If that IS your requirement, is VBA OK?
    Essential, VBA is fine btw. What i need the final result to be is the data within "master" (being the original reference data) to show within the output of "scanning" based on the barcodes coming out of the data within "output". I've attached the final look of what I'm after if that helps.
    Attached Files Attached Files

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Highlight Cell with unique number based off VLOOKUP outcome

    OK. I'll move the thread and step back (I'm a VBA numbskull - others will step in).

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,848

    Re: Highlight Cell with unique number based off VLOOKUP outcome

    Having read and re-read your explanation I am not much the wiser!

    Taking "Scanning": the "lookup" value appears to be a combination of "Conversion" (from "Output" plus "No." from "Master" BUT "Master NO" value go to 168 whereas "Scanning" have a maximumn of 84.

    And as for the logic of the colour coding ....

    Given the examples in your sheet, please explain the above.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  9. #9
    Registered User
    Join Date
    08-18-2019
    Location
    Australia
    MS-Off Ver
    MS Office 365
    Posts
    28

    Re: Highlight Cell with unique number based off VLOOKUP outcome

    Quote Originally Posted by JohnTopley View Post
    Having read and re-read your explanation I am not much the wiser!

    Taking "Scanning": the "lookup" value appears to be a combination of "Conversion" (from "Output" plus "No." from "Master" BUT "Master NO" value go to 168 whereas "Scanning" have a maximumn of 84.

    And as for the logic of the colour coding ....

    Given the examples in your sheet, please explain the above.
    Yeah its a doozy to try and figure out, even harder to explain, but I'll try anyway.

    The overall end result is that "scanning" sheet is automatically highlighted based off the data in the "Output" sheet.

    The colour scheme for what I need has already been determined by the flash data from our supplier thats in the "Master" sheet so nothing needs to change there. The new data I need to input daily is placed in the "Input" sheet manually, they are serial numbers of solar panels. The output sheet will VLOOKUP the data from "Input" and place it in order fropm 0001 - 2456, from that point i need the serial numbers in "output" to find the correct serial number within "master" in order to work out if the value in the L column is a 540 or 535 and change its cell fill to the appropriate colour, and at the same time find the value in the M column and change the text colour in the same cell. Thats the first challenge.

    The next challenge is that the "Scanning" sheet has a unique identifying number for each solar panel. I need it to find its appropriate cell within "Output" can copy the cell fill and text colour. This gives me a heatmap essentially to see quickly and convey whether the correct type of solar panel has been installed.

    I hope this explains it a bit better.

  10. #10
    Registered User
    Join Date
    08-18-2019
    Location
    Australia
    MS-Off Ver
    MS Office 365
    Posts
    28

    Re: Highlight Cell with unique number based off VLOOKUP outcome

    Ok so I was able to solve the first issue.

    I've created a second row below the VLOOKUP data and was able to input a VBA code that gives a return of text colour and background colour based on a VLOOKUP (See code below).

    Still need to have the second issue solved (probably the hardest one really)

    Worksheet VBA Code:

    Sub Worksheet_Change(ByVal Target As Range)
    If CellAdd = "" Then Exit Sub
    RefChk = IntClr & IntShade & FntClr & FntShade
    If RefChk = "" Then Exit Sub
    With Range(CellAdd)
    With .Interior
    .Color = IntClr
    .TintAndShade = IntShade
    End With
    With .Font
    .Color = FntClr
    .TintAndShade = FntShade
    End With
    End With
    IntClr = ""
    IntShade = ""
    FntClr = ""
    FntShade = ""
    CellAdd = ""
    End Sub

    Module1 VBA Code:

    Public IntClr, IntShade, FntClr, FntShade, CellAdd
    Function LookupFormat(ByRef FndValue, ByRef LookupRng As Range, ByRef ColRef As Long)
    Set FindCell = LookupRng.Find(What:=FndValue, LookIn:=xlValues)
    CellAdd = Application.Caller.Address
    With FindCell
    FindRow = .Row
    FindCol = .Column
    With .Offset(0, ColRef - 1)
    With .Interior
    IntClr = .Color
    IntShade = .TintAndShade
    End With
    With .Font
    FntClr = .Color
    FntShade = .TintAndShade
    End With
    LookupFormat = .Value
    End With
    End With
    End Function

+ 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. Replies: 4
    Last Post: 09-21-2022, 10:06 AM
  2. VLookup multiple data in one cell outcome desired
    By sheetfun11 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 16
    Last Post: 06-02-2022, 12:37 PM
  3. [SOLVED] How to highlight a specific cell based on value/text/number
    By Byambadorj in forum Excel General
    Replies: 13
    Last Post: 07-18-2018, 03:41 AM
  4. VBA code to find the row number based on data via input box and the highlight a cell
    By Aditya Sabat in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-21-2014, 05:47 PM
  5. Replies: 13
    Last Post: 12-19-2012, 02:38 PM
  6. Replies: 6
    Last Post: 01-11-2011, 09:43 AM
  7. Create an outcome based on two Cell Entries
    By quasifun in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 11-27-2007, 10:30 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