Match fill colour from adjacent cell

[onefish]

Hi, I have a few hours up my sleeve and I've set out to re-do my gantt chart excel template. The one I did up and have been using for a couple years now is overly complex with many, many hidden helper columns. It also has some annoying limitations which I would like to overcome in the new version. One of these limitations has always been the colour palette available. At the moment I only have a handful of predefined colours with a corresponding reference number. Cells in the chart section that fall between the start and finish range = the fill reference number by simple if statement calculation. I then have conditional formatting apply the fill based on the cell value. It works but it is clunky and limited to the range of pre-defined colours. It also requires a new conditional formatting rule for each fill colour which will get out of control quickly if I try to expand the pre-defined palette much. Another annoying aspect of this approach is that I have to keep a reference chart on the template so I can see what number to enter to get X colour.

OK, enough background..... A far more elegant solution would be to conditionally format the chart cells based on the fill colour I enter in column A (for instance, non-printing). In all me research it doesn't appear that excel has any inbuilt functions that can: 1)return the RGB value of fill in a cell, and 2)fill a cell based on the RGB value. I think this is VB territory here and I have seen several versions of VB code that are supposed to fill a cell. I haven't seen any that are designed to fill it based on the colour of another cell.

I assume also, as this has to be done using VB code, I will have to include the if statement to determine the range of cells to fill (>= cell1, <= cell2). Oh yah, the cells in the chart area contain a string which is used to conditionally format predecessor lines, borders, weekends, etc. So the font colour must be set by the code to match the fill colour (white by default). Something to consider, and I have no idea, is the order of priority: (VB formatting vs conditional formatting vs user formatting). VB formatting probably won't work if it entirely overwrites all conditional formatting. I will still have to use conditional formatting for cell borders on the same cells being filled by VB code.

I hope this isn't too confusing. I know it is a big ask but if anybody has the answers, I would be very grateful.

Cheers!

 

[thisoldman]

A cell's fill color can be determined with this VBA expression: some_cell.Interior.Color. https://msdn.microsoft.com/en-us/VBA/Excel-VBA/articles/interior-color-property-excel

.Color is a decimal integer, not an RGB triplet. Well, its the expression of the RGB hexadecimal triplet as a decimal. I use integer division with a bitwise And in the GetRGBAry function to convert the decimal color to the RGB triplet.

The sub below copies the fill color of cell B2 to the current selection. Then a message box is used to show both the decimal color and the RGB triplet (0–255 scale).

Sub ColorCellSameAs()
''''
' Fills the selection with the fill color of cell B2.
'   Shows a message box with the decimal and RGB values of the color used.
''''
    Dim my_color As Variant
    my_color = Range("B2").Interior.Color
    Selection.Interior.Color = my_color
    
    Dim msg As String
    msg = "Decimal color: " & my_color & vbNewLine & "RGB color: "
    
    Dim rgb_ary As Variant
    rgb_ary = GetRGBAry(my_color)
    msg = msg & rgb_ary(0) & ", " & rgb_ary(1) & ", " & rgb_ary(2)
    MsgBox msg
End Sub
''''

Function GetRGBAry(myColor) As Variant
''''
' Converts a decimal Excel myColor to an RGB 0-255 triplet.
''''
    GetRGBAry = Array(myColor And 255, _
                      myColor \ 256 And 255, _
                      myColor \ 256 ^ 2 And 255)
End Function
''''

Unfortunately, .Interior.Color doesn't pick up fill colors from conditional formatting. For that, the expression that works for me is:
some_condfrmt_cell.DisplayFormat.Interior.Color. https://msdn.microsoft.com/en-us/VBA/Excel-VBA/articles/range-displayformat-property-excel

cell.DisplayFormat.Interior.Color appears to work with cells that are not conditionally formatted. I haven't tested it thoroughly.

 

[thisoldman]

I forgot to mention, vba can handle the borders too.

And to hide cell content I prefer to use the custom number format ";;;" – that's three semicolons, no spaces. That format says: if it's a positive number, display nothing; if it's a negative number, display nothing; if it's zero, display nothing; if it's text, display nothing.

 

[onefish]

Thanks for the advice thisoldman. That number format tip is gold. Means I will need less programming. Assuming I can get the VB code working the way it needs to (will have a go this afternoon), is there a way to make the macro behave like a calculation? I mean monitor cells referenced in the code for changes and perform the macro when changes occur? Event trigger?

 

[onefish]

Looks like event triggering is possible: http://www.excel-easy.com/vba/events.html

Next question though, I really only want to macro to run on the row that is updated, not the whole worksheet. I'm sure there will be a way to select active row in VBA but I foresee an issue with this. If I hit enter instead of tab and the selected cell is not in the row that I just updated then won't the code run on the wrong row? Would I need to do something like a helper column that increases the max number +1 for each row that is updated and then use that max number to determine the correct row for the macro to run on? Perhaps there is an easier way?

 

[thisoldman]

... is there a way to make the macro behave like a calculation? I mean monitor cells referenced in the code for changes and perform the macro when changes occur? Event trigger?

You might tie the macro to a worksheet change event: probably tied to a change in any of the Gantt cell values. Changing a cell's fill color does not trigger a worksheet change event. Pity.

I do a lot of trial-and-error coding when I attempt event-triggered macros. I don't do it often enough to remember the idioms and methods.

 

[RincewindWIZZ]

I forgot to mention, vba can handle the borders too..

Hi,

I am using this stuff to copy conditional format cell fills to an adjacent cell.
It all works fine but .I am having real trouble with the borders which are not all the same and which I need to reproduce.

It seems that copying the fill kills the borders - well actually since the default is no borders, it hides the fine gray line that represents the default borders. OK no big deal - that is oK.

BUT

The first cell I process (H8) happens to have Top and RH borders (single black) but the border properties are all zero
Cells(8,8).Borders.LineStyle = Null
Cells(8,8).Borders(xlEdgeBottom).Color = 0 for Bottom, Top, Left and Right

If I cant read the type of edge in the copy from cell I sure cant get it right on the copy to cell!

So, please what am I doing wrong!

TFAI