combo box inputs creates new boxes

[silverskye787]

i have a combo box that contain range of number from (1-9)

i need some help in creating the yellow boxes automatically after i have choose the number that i want from the combo box

thanks

 

[zzjasonzz]

whats the error?

maybe try deleting the combobox and creating a new one

edit: jindons solution so much smaller lol

 

[usef]

Hi,
you could do that with out vba code.. by useing coditional format.
exut cell B1=1, B2=2...B9=9
link the dropdown to cell "A1"
set the rang to B1:B9
so lets say the cells you wanto change to yellow are C1:C9
select the cell"C1" > format > coditinol format > from the dropdown chose formula> then type =A1>0
and then set the format color you want > click ok
then do the same for cell "C2" but the formula will be =A2>1
=A3>2 ...to a9>10
and that how you doit with out vba..
usef.

 

[silverskye787]

but there's one problem..

there can only have 3 conditional formatting and not more than 3.

 

[silverskye787]

i've tried your help and it works..

can i know how to delete things type in the cell if the cell is not highlighted in yellow

that means i also want to clear the things type in that cell if it is not hightlighted in yellow

 

[zzjasonzz]

Silver,

I was under the impression that you had a combobox, is it just a cell with a dropdown list or an actual combobox object?

 

[zzjasonzz]

If all you have is a drop down list in a cell then right click on the sheet tab with the drop down list and go view code then paste this:

everywhere there is a "' where cell A1 is the drop down list" change the A1 to suit your needs

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Address <> "$A$1" Then Exit Sub ' where cell A1 is the drop down list
    Dim i
    
    Dim dropdownlistrange As Range
    Set dropdownlistrange = Range("A1") ' where cell A1 is the drop down list
    
    If dropdownlistrange.Value <> "" Then
        Range("B3:B11").Interior.ColorIndex = 0
        Range("B3:B11").ClearContents
        Range("B3:B11").Borders(xlEdgeLeft).LineStyle = xlNone
        Range("B3:B11").Borders(xlEdgeTop).LineStyle = xlNone
        Range("B3:B11").Borders(xlEdgeBottom).LineStyle = xlNone
        Range("B3:B11").Borders(xlEdgeRight).LineStyle = xlNone
        Range("B3:B11").Borders(xlInsideVertical).LineStyle = xlNone
        Range("B3:B11").Borders(xlInsideHorizontal).LineStyle = xlNone
        
            Range("B3:B" & dropdownlistrange.Value + 2).Select
            Selection.Interior.ColorIndex = 6
            
            With Selection.Borders(xlEdgeLeft)
                .LineStyle = xlContinuous
                .Weight = xlMedium
                .ColorIndex = xlAutomatic
            End With
            With Selection.Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .Weight = xlMedium
                .ColorIndex = xlAutomatic
            End With
            With Selection.Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Weight = xlMedium
                .ColorIndex = xlAutomatic
            End With
            With Selection.Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .Weight = xlMedium
                .ColorIndex = xlAutomatic
            End With
            If dropdownlistrange.Value <> 1 Then
                With Selection.Borders(xlInsideHorizontal)
                    .LineStyle = xlContinuous
                    .Weight = xlMedium
                    .ColorIndex = xlAutomatic
                End With
            End If
            Range("B3").Select
    End If

End Sub

 

[usef]

for this function you only need one conitinol format for each cell.
if you want me to do it for you in an excel spreadsheet, this is my email "joesph20002001@yahoo.com" i'll be glad to help.