Colour Coding (9 Conditions) 1

[tweek312]

Okay... heres the plot.

I have a simple bar chart setup to display a few bits of data. The bars are color coded to a range of 9 cells in the worksheet and each colour must stick to the contents of the cell. For example: If cell P9 contains the text "Paramount" then I need the color to be beige. The table in which this string is contained will be sorted each time it is updated. If for some reason upon sort the location of the string "Paramount" changes to P10, then I need its according colour to follow or stick to the string. I know this can be done very easily using conditional formatting but I have a total of 9 conditions and of course only three are allowed. I figure there are two ways to look at the task:

1: In the sort force colors to sort just the same as text and formulas.

2: Use VBA to create 9 conditions in which to colour the cells according to given criteria.

My 9 Conditions are:

Paramount
Ventura
Fox
Universal
Pre-production
Paramount
Microsoft
Dreamworks
Independent Studios
Disney

As for colouring criteria I will determine those at a later time. Any generic colours will do for testing purposes.

Thankies!

tW33k

 

[SkipVought]

Hi,

Make 9 new columns for 9 series, one for each criteria, each with it's own color.

Assuming that the FIRST NEW heading is in M1, the Criteria is also in Column C and the Value data is in column B, then in M2...
[tt]
=IF($C2=M$1,$B1,0)
[/tt]
copy across and down



Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[tweek312]

I dont understand how this is supposed to work. Sorry...

Can you please explain further?

 

[AnotherHiggins]

You could use something like this. This code should be placed in the Worksheet object, not a module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set WatchRange = Range("A:C")

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "Paramount"
Target.Interior.ColorIndex = 3
Case "Ventura"
Target.Interior.ColorIndex = 4
Case "Fox"
Target.Interior.ColorIndex = 5
End Select
End If
End Sub

You can add as many Cases as you need. Use the macro recorder to find the numbers for the colors you actually need.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Post a sample of your source data.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[tweek312]

Great dood! Your code works great John. And Congrats on tackling this one before Skip!... You get a pretty little star! =D

Thanks for evry1's help!

tW33k