Conditional Format Multiple Userform Textboxes dependent on value

GhostMonkey72 · Jul 12th 2016

Hi,

I have 58 text boxes all of which can have a value from -32 to 40 and im trying to set a colour of the textbox dependant on its number,

between -32 and -3 is one colour
between -2 on 0 is another colour
and
between 1 and 40 is another colour.

Ive tried using various bit of code but cant quite get anything to work.

Has anyone got any insights to share?

Thanks in advance

The below code is the closest I have got,

I have also posted this thread here http://www.excelforum.com/show…617&p=4427326#post4427326

Code

Sub Fill1()






    Dim ctrlNum As Integer
     
    ctrlNum = 1
     
    For r = 658 To 710
        For c = 1 To 1
            With Userform1.Controls("TextBox" & ctrlNum)
                 
    
                 
                .BackColor = RGB(IIf(ThisWorkbook.Sheets("Data").Range("C" & r).Value <= -3, 218, 255), _
                IIf(ThisWorkbook.Sheets("Data").Range("C" & r).Value = <= -3, 150, 255), _
                IIf(ThisWorkbook.Sheets("Data").Range("C" & r).Value = <= -3, 148, 204))
                 
          
            End With
             
            ctrlNum = ctrlNum + 1
        Next
    Next

Display More

**Ger Plante** · Jul 13th 2016

Re: Conditional Format Multiple Userform Textboxes dependent on value

Quote

cant quite get anything to work....
The below code is the closest I have got

What specifically is not working with this code?

Ger

**Ger Plante** · Jul 13th 2016

Re: Conditional Format Multiple Userform Textboxes dependent on value

This seems to work:

Code

Sub Fill1()






    Dim ctrlNum As Integer
    Dim BC As Long
    Dim r As Integer


    ctrlNum = 1


    For r = 658 To 710
    '    For c = 1 To 1
            With UserForm1.Controls("TextBox" & ctrlNum)
                Select Case ThisWorkbook.Sheets("Data").Range("C" & r).Value
                    Case Is <= -3: BC = RGB(100, 150, 255)
                    Case Is <= 0: BC = RGB(100, 100, 100)
                    Case Is <= 40: BC = RGB(200, 200, 200)
                End Select
                .BackColor = BC
            End With


            ctrlNum = ctrlNum + 1
      '  Next
    Next

Display More

Not sure why you have for C=1 to 1 ... i commented it out.

**Ger Plante** · Jul 13th 2016

Re: Conditional Format Multiple Userform Textboxes dependent on value

And if it doesnt work, please share your workbook so that i can debug it.

GhostMonkey72 · Jul 13th 2016

Re: Conditional Format Multiple Userform Textboxes dependent on value

Excellent works great thank you very much.

Quick question, I have 6 columns of textboxes and if I wanted to apply this to say just the 3rd column how would I change the code?

**Ger Plante** · Jul 13th 2016

Re: Conditional Format Multiple Userform Textboxes dependent on value

Assuming your text boxes are numbered incrementally from 1 to 52 (or whatever) then I presume the third column of text boxes is related to certain rows of cells... if so, you would just check the value of r (the row counter for the cells) and calculate the text box number from this.

GhostMonkey72 · Jul 13th 2016

Re: Conditional Format Multiple Userform Textboxes dependent on value

Brilliant thank you,

Thanks for you help, much appreciated.

Conditional Format Multiple Userform Textboxes dependent on value

Participate now!

Share

Users Viewing This Thread