Conditional formatting in text boxes

[Roger on Excel]

I have textboxes on a userform.

The text boxes read (and write) to cells A1:A60

Adjacent to these cells I have a comments column which is filled in for
particular rows

Depending on whether the adjacent cell in column B has an entry, is there a
way to make the textboxes change their font attributes? For example, become
BOLD when there is a comment.

Can anyone help?

 

[Jacob Skaria]

You should have posted your code..Within your loop add a line something like
the below..

Dim intCount as Integer
For intCount = 1 To 60
Me.Controls("Textbox" & intCount).Font.Bold = _
Not (Range("A" & intCount).Offset(, 1).Text = "")
Next

 

[Ryan H]

Since you didn't post any code you probably have lots of options. Do you
change the values of the cells using the Userform interface or the worksheet?


If the worksheet and you didn't change the default Textbox names, then you
probably want to setup a Worksheet_Change Event call a sub like this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = "B" Then
Call TextBoxBolding(Target.Row)
End If

End Sub

Sub TextBoxBolding(rw As Long)
Me.Controls("Textbox" & rw).Font.Bold = Not Cells(rw, "B").Text = ""
End Sub

Or you could put Jacob's code in your Userform_Intialize Event.

Private Sub UserForm_Initialize()

Dim i As Long

For i = 1 To 60
Me.Controls("Textbox" & i).Font.Bold = Not Cells(i, "B").Text = ""
Next i

End Sub

Hope this helps! If so, let me know, click "YES" below.

 

[Roger on Excel]

Dear Jacob,

Thankyou for the code - it works very nicely.

I was able to modify it for my purposes relatively easily. Apologies for
not including the code I am using.

I have a question - how does one change other text attributes using this
code? for example if I want to change the the color to red or even change the
font?

Best regards,

Roger

 

[Ryan H]

Put this in your Userform Intialize Event

Private Sub UserForm_Initialize()

Dim i As Long

For i = 1 To 1
With Me.Controls("Textbox" & i)
.Font.Bold = Not Cells(i, "B").Text = ""
.BackColor = 255
.Font.Name = "Tahoma"
End With
Next i

End Sub

 

[Ryan H]

Correction,

Private Sub UserForm_Initialize()

Dim i As Long

For i = 1 To 60
With Me.Controls("Textbox" & i)

' bold or unbold font
.Font.Bold = Not Cells(i, "B").Text = ""

' change font color
.BackColor = 255

' change font name
.Font.Name = "Tahoma"
End With
Next i

End Sub


Hope this helps! If so, let me know, click "YES" below.