Formatting for the whole worksheet

[Inglesina94]

Hi all, I'm stuck and need some help.

I have a worksheet that we use to track our clients and the progress of the
jobs, with one job per row.

Column B represent the job status (In Progress, On Hold, Completed ... 5
options in total). Column C represent the client with a 3 letter code.

I would like to:
- Change the colour of the row in relation to the client code - each client
will be associated a colour (I will then amend the formula to add new
clients/colours as new ones are added)
- Maintain the background colour and change the text to bold if the value in
cell B for that row is In Progress.
- Maintain the background colour and change the text to italic (not bold) if
the value in cell B for that row is On hold.
- Delete the background colour of the row if the value in cell B for that
row is one of 2 options - Stopped or completed

In practice, I would like all the rows of live projects to be coloured in
relation to their relevant client colour with the projects in progress is
bold or in italic if the job is on hold. Once the jobs are completed or
stopped, all conditional formatting to be removed.

I would like all of this to happen automatically as changes are made to the
worksheet, either within existing cell or when new projects are added.

Any help very much appreciated!

Re.

 

[The Code Cage Team]

What if you exceed the amount of colours with clients i.e 50 standar
colours 60 clients, you will have 10 that you cannot colour (well no
without a bit of fiddling!)

--
The Code Cage Tea

Regards,
The Code Cage Team
http://www.thecodecage.co

 

[Inglesina94]

Thanks for pointing this out to me as I did not realise there was a maximum
of 50 colours ... though I do not think this will be an issue as it is very
very unlikely we are going to have live projects with more than 540 different
clients and once the projects are completed the colour differentiation is
irrelevant.

 

[The Code Cage Team]

Sorry my bad, its 56 colours any way to check the colours and what the
look like on a sheet put this sub in a standard code module and run:

Sub Colour_Index_sheet()
Sheets.Add
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = 0 To 56
Cells(i + 1, 1).Interior.ColorIndex = i
Cells(i + 1, 1).Value = i
Next i
Range("A29:A57").Cut Destination:=Range("B1")
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

If you can cope with the similar colours then it may be possible,
please join our forums where you can upload a workbook which we can look
at for you, remember to change any sensitive or personal data.


--
The Code Cage Team

Regards,
The Code Cage Team
http://www.thecodecage.com