How do I highlight cells like they are in a formula?

[frabulator]

I dont really know how to ask this, and because of that I wasnt able to find correct answers on Google.

I would like to select multiple cells and have them selected in different colors, like when you type in a formula in a cell address bar. Its not really selecting, its more like, just indicating what cells are going to be used.

My goal is to have this linked to a userform that, when a different addressed is entered into the userform, the cell selection for that color changes to that new address.

Basically, its like the formula selection coloring effect, but via userform.

Is that possible?

 

[James006]

Hi,

Do you mean like Select multiple cells with the Control key ?

 

[awoohaw]

I don't htink you can have cells highlighted like that without going into edit mode in a cell.
But, instead of highlighting cells, you can use the "Trace Precedents" feature in the Formulas Ribbon, in the Formula Auditing Section

I'm not sure that fits in a user form scenario though.

 

[awoohaw]

in your user form Are the number of possible cells fixed? Will a user type in the cell address of the cell being used in the text boxes of the user forms? (for these text boxes, no other data). If so, maybe VBA could highlight the cells as the user enters data into the form. will the ultimate formula be consistent?

 

[frabulator]

I don't htink you can have cells highlighted like that without going into edit mode in a cell.
But, instead of highlighting cells, you can use the "Trace Precedents" feature in the Formulas Ribbon, in the Formula Auditing Section

I'm not sure that fits in a user form scenario though.

Yeah, I thought about that, but its not exactly what I am looking for. I dont think it would work for my situation.

Hi,

Do you mean like Select multiple cells with the Control key ?

Not exactly. That could work, but if the values are separated (like A1 and C1) I would like the two options to be different colors to better differentiate between the selections.

in your user form Are the number of possible cells fixed? Will a user type in the cell address of the cell being used in the text boxes of the user forms? (for these text boxes, no other data). If so, maybe VBA could highlight the cells as the user enters data into the form. will the ultimate formula be consistent?

Yes, there will be a total of two designated textboxes for these values. One for the input, one for the output. The textboxes are cell addresses. The user can select the input range and output range. They could be together (A1:A2) or separated (A1 and C1).

The issue with highlighting cells is what about the cells previous formatting or if the cell was inside of a table? I do not want to undo formatting users might have previously made.

 

[frabulator]

I think I figured out a work around. While not what I wanted, I have changed the interior colorindex of the cells in question. I first saved the cells color and boarder formatting, then, once selected, change the background to a specified color. When the cell is deselected, or the userform closes, then the cells reverts back to its previous color.

Public S_R As String
Public S_A As String
Public S_P As String


Public R_R As String
Public R_A As String
Public R_P As String

Sub SelectInput()
    
    On Error Resume Next
    Range(S_A).Interior.ColorIndex = S_R
    S_R = Range(TextBox2.Text).Interior.ColorIndex
    
    Range(S_A).Interior.ColorIndex = S_R
    Range(S_A).Interior.Pattern = S_P
    
    
    S_A = TextBox2.Text
    
    
    S_P = Range(TextBox2.Text).Interior.Pattern
    Range(TextBox2.Text).Interior.ColorIndex = 24

    On Error GoTo 0
    

End Sub

Sub SelectExport()

    On Error Resume Next
    Range(R_A).Interior.ColorIndex = R_R
    R_R = Range(TextBox3.Text).Interior.ColorIndex
    

    Range(R_A).Interior.ColorIndex = R_R
    Range(R_A).Interior.Pattern = R_P
                
                
    R_A = TextBox3.Text
    
    
    R_P = Range(TextBox3.Text).Interior.Pattern
    Range(TextBox3.Text).Interior.ColorIndex = 22
    
    On Error GoTo 0

End Sub

Sub DeselectCells()

    On Error Resume Next
    
    Range(S_A).Interior.ColorIndex = S_R
    Range(S_A).Interior.Pattern = S_P
    
    Range(R_A).Interior.ColorIndex = R_R
    Range(R_A).Interior.Pattern = R_P
    
    On Error GoTo 0
    

End Sub

Private Sub UserForm_Terminate()

    DeselectCells
 
End Sub

I would still prefer to have a highlighted cell function like the formulas do in the cell properties. If anyone knows how to do that please let inform me !