Can I mark a cell as being empty eventhough there is a value in it?

[MaddogJason]

I need to be able to quickly, 2-3 clicks or a macro, be able to somehow show the value in the cell but make excel read it as if the cell was empty. For example, if I could with one-click colour a cell yellow, I could still see the value, but a function in another cell would now see the original cell as empty.

 

[jasonb75]

For example, if I could with one-click colour a cell yellow, I could still see the value,

I'm not sure that I follow, your example suggests that you're using the same colour for the cell and the background to hide the value from view. If that is the case then the value is already visible to the function in the other cell. Formatting the cells to make the values invisible does not hide them from functions.

 

[MaddogJason]

I'm not sure that I follow, your example suggests that you're using the same colour for the cell and the background to hide the value from view. If that is the case then the value is already visible to the function in the other cell. Formatting the cells to make the values invisible does not hide them from functions.

Sorry, not the best explanation.
Let's say I take sum from range A1:A4.
I would like to be able to somehow indicate that I want to exclude the cell A2 from the sum, but still, see the value in the cell.
For example, have a function that would make excel read the cell as empty if the value of that cell has a bold font, or a specific colour, or is underlined.

 

[jasonb75]

I found this function which looks like it sums cells based on the colour of a reference cell. Is this something you can use?

For example (if I'm following the code correctly), this formula, when used with the code, will sum all cells in B1:B10 that are the same colour as A1.

=SumColor($A$1,$B$1:$B$10)

Probably best used with a double click event to alternate between 2 colours as required. As changing cell colour is not an event that would trigger calculation, you would need to force calculate after the colour change has been made. Application.Volatile may also be required.

'Sum by Colors    
Function SumColor(rColor As Range, rSumRange As Range)    
    ''''''''''''''''''''''''''''''''''''''    
    'Written by Ozgrid Business Applications    
    'www.ozgrid.com    
    'Sums cells based on a specified fill color.    
    '''''''''''''''''''''''''''''''''''''''    
    Dim rCell As Range    
    Dim iCol As Integer    
    Dim vResult    
        
    iCol = rColor.Interior.ColorIndex

    For Each rCell In rSumRange    
        If rCell.Interior.ColorIndex = iCol Then    
            vResult = WorksheetFunction.Sum(rCell) + vResult    
        End If    
    Next rCell    
        SumColor = vResult    
        
End Function

 

[MaddogJason]

This looks interesting. I'll give this a try. Cheers!