Conditional Formatting Multiple Forms

[Damo1412]

Hi,

I have developed a database for work with (a lot) of help from this forum to record and process jobs.

As the database has developed, there are now a lot of forms for different inputs / searches etc. On quite a few of these forms, I have created a sort of conditional formatting using VBA meaning that our biggest customers are allocated a colour which is used throughout the office to quickly recognise the customer.

When the user selects this customer from a combo box, certain fields change colour to match the client's designated colour.

The code I use is:

If Me.ClientURNComboBox = 202 Then
Me.Text113.BackColor = RGB(255, 153, 255)
Else
If Me.ClientURNComboBox = 166 Then
Me.Text113.BackColor = RGB(164, 213, 226)
Else
If Me.ClientURNComboBox = 147 Then
Me.Text113.BackColor = RGB(253, 140, 0)
Else
If Me.ClientURNComboBox = 175 Then
Me.Text113.BackColor = RGB(255, 255, 153)
Else
If Me.ClientURNComboBox = 201 Then
Me.Text113.BackColor = RGB(214, 234, 175)
Else
Me.Text113.BackColor = RGB(255, 255, 255)
End If
End If
End If
End If
End If
Me.Refresh

This code works fine but occasionally the amount of work from a particular client increases or decreases meaning that they may drop off or be added to the list of the "big 5".

When this happens, I have to manually change the code on a number of forms. Is there a way that I can enter the default colours on a table or similar where each form checks the table to show which colour (if any) to apply to the formatting.

Thanks for your help

 

[Ranman256]

put the colors in a table, (not code)
on the client record, put 1 field,RGB
then you would just alter the RGB values on the record, and IT changes the colors.

r = left([RGB],2)
g = mid([RGB],3,2)
b =right([RGB],2)

.color = RGB(r,g,b)

 

[Damo1412]

Thanks Ranman,

I'm not completely sure what you mean.

Do I add a new Field Name to the "Client" table called "RGB" and for the relevant client enter this as "255, 153, 255" or whatever colour I want it to be.

Finally change the VBA on the forms to read:

Me.ClientURNComboBox = .colour

 

[Damo1412]

Thanks,

I've got it now