+ Reply to Thread
Results 1 to 4 of 4

QR code scan to change colour of cell

  1. #1
    Registered User
    Join Date
    11-17-2019
    Location
    Ireland
    MS-Off Ver
    2019
    Posts
    3

    QR code scan to change colour of cell

    Hi,

    I am a steel fabricator and trying to tidy up the tracking of work in process.

    We currently have a simple excel schedule with job numbers and columns for each process that this job goes through. When complete I highlight the cell green.

    Is there a way of having a cover sheet with each drawing pack, with a QR code for each process, and when the relevant QR code is scanned by the workers, the cell turns green?

    Maybe a big ask, but any advice would be welcome.

    Thanks

    Screenshot 2022-06-01 100739.png

  2. #2
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    957

    Re: QR code scan to change colour of cell

    Do you have a code reader?

    Does it output into your Excel Doc?

    Do you know the Outputs?

    IF yes to all the above then absolutely - provided you have the code reader writing to each cell as it scans and you setup some conditional formatting to flip to certain colors based on the scan.

    This is a tall ask and comes in with assumptions that you are already scanning and processing.

    But if you want to generate a code and have the team scan with their phones or something related... then that will not work... you need a mechanism to not only read, but communicate to the book that a specific Item is completed... this would require that the code be generated in a location that the book could consume it and write it to the appropriate location within the book. This would likely be a 3rd party application OR some VBA that takes a new code as it is scanned and does a lookup to set appropriate statuses.

    Is any of that sounding like what you have going... or do you just have a spread sheet with no scanner...
    -If you think you are done, Start over - ELeGault

  3. #3
    Registered User
    Join Date
    11-17-2019
    Location
    Ireland
    MS-Off Ver
    2019
    Posts
    3

    Re: QR code scan to change colour of cell

    I have very little at the moment, only a google spreadsheet with about 300 rows of the above screenshot.

    I had originally planned on just using phones, however I had an idea this would be difficult, if we need code readers I could certainly purchase them.

    So if we had code readers would you have an idea of how generate a code for each column of each row? My general idea is to have a cover sheet on each job with QR codes for each process (something like the attached image).

    Attachment 782637

  4. #4
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    957

    Re: QR code scan to change colour of cell

    This would take a bit of setup... The Code reader would scan and write to a field. But there is nothing in a code that will dictate which code...

    However - combination of VB and code Reader you could make this work... IF.. the codes being scanned are on the sheet -

    What the call to action can be is on scan it enters the value into a field, the worksheet can see that the field is updated and kick off the code to look up what was scanned and apply any number of actions you then dictate.

    Scanning on a phone... I dont know that there would be a way to link that to your sheet directly...HOWEVER... if you were to give everyone the ability with their phone to scan/collect lists of codes you could have them upload that list and process it after... this could also work in the same fashion only instead of doing it live you are reconciling at the end of the day everything that was scanned...

    Really just need to get creative - but the first thing you need to figure out is the tool and data collection.

    Whatever your choice is, where is the data stored "ON SCAN" - is it connected to the computer with the master list, or is it writing to a log on the device itself.

    Then from there you will be able to write the code to consume the input (Live or against the repository (Lists)) -

    The actual code to write against it will have to be custom to your end file - Highlighting and lookups are simple enough with VB - its just figuring out your process of real world ID's to digital ID's

    I know that we have used Scanners to handle Outgoing and Return mail and both were setup on different methods. Some would scan directly into sheets (Return Mail) others would be scanned and stored then downloaded from the log that the scanner generated that would be imported into a DB later used for reconciliation...

    Does it need to be live?
    - On Scan, update sheet and color it

    Or can it be end of day task
    - Download Scans to file and drop in the sheet that then updates all found...

    Both methods can update the sheet with code, or if all we are doing is highlighting you can have it do conditional formatting if it is found in the Scanned list on a given tab and highlight accordingly... as well as different shades if found multiple times (Duplicate scans) ...

    Shoot for that matter... you can just have a column that is a 1/0 that you enter manually when you go throughout the day... enter 1 and it will highlight off conditional formatting...

    This is all really a choice of entry and how you wish to present the document with values to indicate it is complete... all workable but first need to sort out the input choice

+ 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: 3
    Last Post: 06-14-2014, 08:15 PM
  2. Cell colour change code not activating
    By Groovicles in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 06-10-2014, 03:28 PM
  3. VBA code to tell an object to change colour depending on figure in cell
    By Muzza86 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-29-2014, 07:29 AM
  4. Replies: 3
    Last Post: 09-14-2013, 02:22 AM
  5. [SOLVED] VBA to immediately change the colour of a cell depending on the code placed in another cel
    By Phil Payne in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-13-2013, 01:15 PM
  6. Using VB Code to change cell colour on back of formula
    By Twaddy in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 09-30-2010, 04:29 PM
  7. VB Code to change cell colour by entering a word
    By Twaddy in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-24-2010, 07:38 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