Forum Discussion

Laurie McDowell's avatar
Laurie McDowell
Copper Contributor
Jun 15, 2017

Formula or function for IF statement based on cell color

I don't know how to code in VBA but am trying to automate an if/then calculation based on cell color. As shown in the picture, if the colors of the cells in column B are the same as those in Column G...
Formulas & Functions
Need Help
  • Kevin Lehrbass's avatar
    Kevin Lehrbass
    Jun 15, 2017

    Step 1 Paste code (found at bottom) into a new module. ALT F11 shortcut should open the code area.
    Step 2 In cell O1 paste formula: =InteriorColor(B1) drag formula down
    Step 3 In cell P1 paste formula: =InteriorColor(G1) drag formula down
    Step 4 In cell L1 paste formula: =IF(O1<>P1,F1+K1,ABS(F1-K1)) drag formula down

     

    Does this work for you Laurie?

    Cheers,

    Kevin

     

    Function InteriorColor(CellColor As Range)
    Application.Volatile
    InteriorColor = CellColor.Interior.ColorIndex
    End Function 

     

Phili0105's avatar
Phili0105
Copper Contributor
Apr 06, 2022

I have a spreadsheet with employees, their name, details and their status (on shift, annual leave etc). Now i need to check in our WFM Software if there have been any new Leave requests and them change the status column for that specific employee, if they have called in sick.

Can someone help me? Paste all entries from WFM (aspect) showing annual leave or sick leave on the side of my spreadsheet. Then I applied conditional formatting so it highlights and employee ID number that is a duplicate (it is on original employee list and also in the leave list from WFM). I now want to use a macro or formula to do something like: if cell is highlighted, then change the status column of that row to the value found in the this same employees status column of the copied WFM table.

Is that possible at all?

Resources