Conditionally format labels on Userform

I made a UserForm with two textboxes and two labels. Textbox1 is linked to a worksheet and the input from Textbox1 is input in a worksheet, the Lookup is done on the worksheet. A cell on the worksheet has the answer and is linked to Textbox2 and once the label CALC has been pushed appears in Textbox2. The other label on the UserForm is to exit the form.

On the form I made a lot of labels to simulate a paper table the user is used to. The labels have a with statement. On initialize of the form the labels get their Forecolor and contents from the worksheet. Furthermore the labels get centered horizontally and vertically on a kind of background label. All this is working.

The one thing I'am trying to accomplish is to conditionally change the forecolor of the labels from a , hardly readable green, to a bright green. The reason behind this so the user can see where the outcome is derived from in the table. Furthermore it has to change back once another calculation is being made.

My first thought was to append some kind of code to the CALC label and compare the outcome already present in Textbox2 to the Caption of the labels and change the labels caption, i.e forecolor. Can this be done? Compare a Caption to a Textbox value and then change the forecolor by means of code.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Upload a worksheet showing the issue.
faraoosirisAuthor Commented:
Open the Userform frm300ER from sheet1(blad1) bij clicking the button in cell C6.
This will open a new useform and you have to choose B777-300ER and push start.

This will open the Userform frm300ER I talked about. Fill in a value between 16.4 and 29 at the MACZFW and  push CALC in the upper right corner.

The form frm300ER can also be found by alt-F11 and choose frm300ER. By hitting F5 you will go the form layout.

It all is in a design state so codes and vba look not that professional. The 2 command buttons are there to zoom in and out. This because I design the form on a smaller resolution screen then the user has.
faraoosirisAuthor Commented:
Is there a way by means of an Array to fill labels so they are consistently renamed and still make sense to me as to the content of the label.

I found this code

For i = 1 To 2
    With Sheets("blad5")
        If .Cells(i, "A").Value = "NO DATA" Then _
          Me.Controls("lbl" & i & "Af").BackColor = RGB(200, 200, 200)
        Me.Controls("lbl" & i & "Af").Caption = .Cells(i, "A").Value
    End With
Next i

and adjusted it to lbl instead of Label1, Label2... and put Af to it. As it turns out I can now
fill lbl1Af lbl2Af but how about lbl1Af, lbl1Bf, lbl1Cf upto lbl4Df. This way the labels would represent the cells the data is taken from.

Any suggestions?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Sorry! I spend more time to understand the design.
To help in this tell what to do step by step.
From opening workbook, to entering data, and tell what you see and what to expect.

Manually color the labels or text boxes you want to work with.

This may invite other people to contribute.

Upload the final state of the workbook.
faraoosirisAuthor Commented:
As the excel program opens on the first worksheet there is a button command1 situated on cell "C6".

This command button opens a Userform.
On this form, for now, the user can choose via a radio button. I'am currently only developing the B777-300 UserForm. The difference between the two forms will eventually be just different values.

As the frm300ER opens, the user can give an input in textbox1. The lookup of the value to be returned is done on Blad4. The value is displayed in Textbox2 after pushing the CALC label in the righthand corner.

The input and the returned value are displayed on the righthand side on the green background and in white font. This will eventually be the only outcome being displayed. The Textbox2 I will hide.

This all works fine. The only thing I want to do now is to highlight the found value's.
For example 26.3 in Textbox1 will return 0.3 in Textbox2 and underneat each other on the green background.
In the center of the form you see  25.6-26.5 and to the right of it 0.3. This 0.3 is the answer and the value 26.3 is in the bracket of 25.6-26.5.

I want to hightlight 25.6-26.5 and 0.3 and the header of the colum MACZFW(%) and

faraoosirisAuthor Commented:
I solved the problem by using the camera tool instead of filling the label's caption.

The camera takes a real time "picture" of the worksheet including the conditional formats.
I did use some helper colums to "highlight" the colum headers. I used an IF statement to determine if the values were TRUE or FALSE , equal to the value input in the worksheet, and used the SUM function of the colums the "highlight" the colums.

The other helper cell is to read a 0 or 1 from the UserForm. The on the mousedown event of TextBox1 generates a triggger to "repaint" the picture in the Image frame. I also use it at startup of the form to clear the form of it conditional formats.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.