4 Replies Latest reply: May 21, 2013 7:43 PM by Bruchare
gmcra19 Level 1 Level 1 (0 points)

How can I use the functions IF/Then to change the color of a cell?

i.e. use a scanned barcode: search the document/ colum , if found change the color green.


Numbers, OS X Mountain Lion (10.8.2)
  • 1. Re: How can I use the functions IF/Then to change the color of a cell?
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    GM,

     

    The actual change of format will be done by the Conditional Format feature in the Cells Inspector. It's possible that there will be a role in that for the IF function, depending on the approach you choose.

     

    If I follow your description, you will have a cell with the result of a barcode scan and you want that cell's background fill to change if it represents a value that is found in a range of other cells.

     

    The exact implementation would depend on how the scan code got there. If by direct entry, you would have to have an auxiliary cell to compute the match. If the code is transferred from elsewhere it would be possible to do the conditional format directly.

     

    Jerry

  • 2. Re: How can I use the functions IF/Then to change the color of a cell?
    gmcra19 Level 1 Level 1 (0 points)

    Kinda correct, for example: I have a list of serial numbers from personal devices for a business, using the QR Code scanned into say colum "A" I need Numbers to find the scanned serial code in Colum "B" and change the color background to GREEN, If it cant find it, ignore it. 

     

    I will be using a QR Barcode for the input.

  • 3. Re: How can I use the functions IF/Then to change the color of a cell?
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    You'll be best served if you add a column to the QR Code table, and Hide the actual data table. I suggest that the list of personal device serial numbers be in a separate table. There's just no need to try to integrate the database with the scan data into a single table. Same sheet is fine, but I'd keep the tables separate. Let's call the scan data table Scan and the historical inventory table History.

     

    in column B of Scan, write:

     

    =IF(COUNTIF(History :: A,A)>0, "   "&A, A)

     

    Here's the screen shot:

     

    Screen Shot 2013-02-17 at 8.51.49 am.png

     

    I used three spaces ahead of the copied QR Code to detect found codes. I could have used another character or number of characters. I used three so that it would cause an offset in the alignment, making the detected addresses more obvious before applying the formatting.

     

    The Results column has a Conditional Format: Text starting with three spaces gets a Green background and a value of zero gets a faint font color. You can adjust these signals any way you wish.

     

    Jerry

  • 4. Re: How can I use the functions IF/Then to change the color of a cell?
    Bruchare Level 1 Level 1 (0 points)

    The QR Code 2D encoder for Numbers encode data to qr barcodes in Numbers.