Cell Dependant Format using combo box

[LearningExcelAgain]

I will start by saying that I have successfully gotten a couple of VBA macros to work but I do not know what I am doing...

I have added some combo boxes into a spread sheet. These boxes reference cells in a column that is defined. I can get the selection to work ie. I select Good from the list and it brings it into the list box. The issue that I am having is that I want the text color to follow as well. The words in the list are formatted as follows:
Good (Green)
Bad (Orange)
Ugly (Red)
When I select any of the words it comes across as just black text without the underlying color. Also since the individual selections have a different preferred color setting the format of the box will not do what I want.

any ideas on this?

 

[My Aswer Is This]

I'm a little confused.

You first said:

I have added some combo boxes into a spread sheet
I select Good from the list and it brings it into the list box​

So your saying when you choose Good from the Combobox you want the word Good entered into a Listbox
and you want the word Good displayed in the listbox with the font color being Green.

Is that what you wanting?

If that is what your wanting I do not believe that is possible

And it's always best to tell us what type Combobox and what type Listbox

We have Activex Comboboxes and Form Control list boxes and UserForm Comboboxes and listboxes

And it's always best to provide the names of these controls.

Like I have combobox named Combobox1 and Listbox named Listbox8

 

[My Aswer Is This]

I also see you said:

I select Good from the list and it brings it into the list box.​

But your subject title for your posting says:

Cell Dependant Format using combo box

So are you saying if you select Green you want the text entered into a cell and you want the font color in the cell to be green?

The problem is you said:
I select Good from the list and it brings it into the ​

list box
​

you did not say​

cell

​

​

 

[LearningExcelAgain]

Clarification of the questions you asked.

I am using an ActiveX combo box control. I have it linked to a list in the spread sheet. The font colors of the individual choices are set to the preferred colors in the cells. What I want to do is have these font colors follow the selection into the combo box display field.

The combo box works as expected. When I drop down the list and select one of the items I do get that item displayed in the combo box field. The intent of the coloration is to draw immediate attention to the choices made (this is going to be printed and customer signed).

 

[MickG]

Assuming your "Combobox" is on your active sheet and is not filled by the "Listfillrange" property then.

You can fill your combobox by the first code below.
This assumes your data is in column "A" , Alter range address in code to suit.

This will fill the combobox with your data , and will fill a second column with the colour of the individual cells.

[COLOR="Navy"]Sub[/COLOR] MG30May23
[COLOR="Navy"]Dim[/COLOR] R [COLOR="Navy"]As[/COLOR] Range
ComboBox1.Clear
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] Range("A1:A10")
    [COLOR="Navy"]With[/COLOR] ActiveSheet.ComboBox1
        .AddItem R.Value
        .List(.ListCount - 1, 1) = R.Interior.Color
     [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] R
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Using the code below for your Combobox Change event will change the Combobox.Backcolor to the appropriate colour,
based on the colour shown in column 2 of the combobox.

Private Sub ComboBox1_Change()
With ComboBox1
    .BackColor = .List(.ListIndex, 1)
End With
End Sub

Regards Mick