Highlight Cell with unique number based off VLOOKUP outcome

[Shonky]

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

[Glenn Kennedy]

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 Kennedy]

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.

[Shonky]

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

[Glenn Kennedy]

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?

[Shonky]

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.

[Glenn Kennedy]

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

[JohnTopley]

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.

[Shonky]

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.

[Shonky]

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