ListBox Transfer depending upon Cell Interior Colour

[MSingh]

Hi,

How can i transfer the item selected in Listbox1 to the first empty cell in
Sheet1 Range("J14:J213") with Interior.Color = RGB(128, 128, 128).
The interior colour of cells in Range("J14:J213") are either white or grey depending
on whether the adjacent cell in I14:I213 has a value greater than 0.

Thanks again
Mohamed

[HansV]

You could use code like this, for example in the On Click event of a command button:

Code: Select all

  Dim r As Long
  For r = 14 To 213
    With Range("J" & r)
      If .Value = "" And .Interior.Color = RGB(128, 128, 128) Then
        .Value = ListBox1.Value
        Exit Sub
      End If
    End With
  Next r

[rory]

The interior colour of cells in Range("J14:J213") are either white or grey depending
on whether the adjacent cell in I14:I213 has a value greater than 0.

If you are talking about Conditional Formatting, then you should test the value in the adjacent column, because the Interior.Colorindex will not reflect colours applied by CF. (if you are using 2010, you can refer to the DisplayFormat instead).

[MSingh]

Hi Hans,

Thanks again-code worked perfectly!

Thank you Rory - I will heed your advice.

Kind Regards
Mohamed