Cell Colour Change

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Craig Coope - 18 Apr 2007 10:31 GMT

Hi,

I have this code

With Sheet1.Range(Runinfo1.ComboBox2.Value)

If .Range("b2") <> "" And .Range("b7") = "" And OptionButton2.Value = True
Then
.Range("b9").Interior.ColorIndex = 5
End If

I'm trying to change the colour of the cell if option2 button is selected
(after my command button is pressed) but it doesn't work. I'm assuming it is
the ".Range("b9").Interior.ColorIndex = 5" part...

Any ideas?

Cheers...

Reply to this Message

Barb Reinhardt - 18 Apr 2007 11:14 GMT

You may want to look at ths construction of this:

I'm not sure about this
with sheet1.range(Runinfo1.ComboBox2.Value) but you can't put .range after
it, I suspect. Try something lik the following

With Sheet1
If .Range("b2") <> "" And .Range("b7") = "" And OptionButton2.Value = True
Then
.Range("b9").Interior.ColorIndex = 5
End If

> Hi,
>
[quoted text clipped - 14 lines]
>
> Cheers...

Reply to this Message

Craig Coope - 18 Apr 2007 11:42 GMT

Thanks for your reply...

I'm not sure exactly what you mean but I was sure the "with" part of my code
was correct. I can't really change it because I need it to identify which
cell I want to change (depending on the status of a combobox elsewhere.) If
I didn't have that I'd have to duplicate all my code many times.

eg...this code works

With Sheet1.Range(Runinfo1.ComboBox2.Value)

If .Range("b2") <> "" And .Range("b7") = "" Then
.Range("b7") = TextBox1.Value
ElseIf .Range("b2") <> "" And .Range("b7") >= 0 And .Range("b13") = "" Then
.Range("b13") = TextBox1.Value
ElseIf .Range("b2") <> "" And .Range("b13") >= 0 And .Range("b19") = "" Then
.Range("b19") = TextBox1.Value
ElseIf .Range("b2") <> "" And .Range("b19") >= 0 And .Range("b25") = "" Then
.Range("b25") = TextBox1.Value
ElseIf .Range("b2") <> "" And .Range("b25") >= 0 And .Range("b31") = "" Then
.Range("b31") = TextBox1.Value
End If

it doesn't change the colour of the cell but it inputs the number displayed
in the textbox.

I just assumed in my origianl code that the

.Range("b9").Interior.ColorIndex = 5

syntax was wrong

ok I have a simple question.

If I put

if optionbutton1.value = true then

what would follow if I wanted cell A1 to turn a different colour?
I assumed it would be something like

if optionbutton1.value = true then
range("a1").Interior.ColorIndex = 5

Thanks again...

----- Original Message -----
From: "Barb Reinhardt" <BarbReinhardt@discussions.microsoft.com>
Newsgroups: microsoft.public.excel
Sent: Wednesday, April 18, 2007 11:14 AM
Subject: RE: Cell Colour Change

> You may want to look at ths construction of this:
>
[quoted text clipped - 26 lines]
> >
> > Cheers...

Reply to this Message

Barb Reinhardt - 18 Apr 2007 13:16 GMT

I'd probably use something like this

if optionbutton1.value then 'I presume this returns = TRUE or FALSE.
Sheet1.range("a1").Interior.ColorIndex = 5
'Identify the sheet somehow so that you know for sure which range
it's changing.
end if

> Thanks for your reply...
>
[quoted text clipped - 81 lines]
> > >
> > > Cheers...

Reply to this Message

Dave Peterson - 18 Apr 2007 13:41 GMT

Are you sure that .range("B2") works the way you want--you could have a
problem--or it could work exactly the way you want.

If runinfo1.combobox2.value points at A1, then your code will look at B2 and B7
of that worksheet.

But if runinfo1.combobox2.value points at c27 (say), then your code will be
looking at D28 and D33 of that worksheet.

Try this from the immediate window:
?range("c27").Range("B7").Address
You'll see D33 returned.

If that's what you wanted (one column to the right and one row down, then
.range("b2") is perfect.

Other than that, your code worked ok for me.

I put all this behind the userform named Runinfo1:

Option Explicit
Private Sub CommandButton1_Click()
If Me.ComboBox2.ListIndex < 0 Then
Beep
Exit Sub
End If
With Sheet1.Range(Me.ComboBox2.Value)
If .Range("b2") <> "" _
And .Range("b7") = "" _
And OptionButton2.Value = True Then
.Range("b9").Interior.ColorIndex = 5
End If
End With
End Sub
Private Sub UserForm_Initialize()
With Me.ComboBox2
.AddItem "a1"
.AddItem "c27"
.AddItem "F3"
End With
End Sub

You sure you didn't want to clear .range("B9") in an else statement?

> Hi,
>
[quoted text clipped - 14 lines]
>
> Cheers...

Signature

Dave Peterson

Reply to this Message