55 x conditions for Conditional formatting with a twist

[kosh]

What I am trying to do is to lookup the values in Column C and then to fill the background colours in the >0 values in the matching row of columns (v:BX).

The numbers in Column C range from 1 to 55 and I require a unique colour for each number in the range (V:BX) which has many blanks inbetween numbers on the same row.

I tried the following the VB below I picked up from a friend, but I tried to adapt it using a range offset but this just coloured the entire range of (V:BX)

I also tried to write a function in VB but I am not that proefficient in VB and failed.

Any help is very appreciated.

Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
Set color = Range("C6:C1000")
For Each Cell In color

If Cell.Value = 1 Then
Cell.Interior.ColorIndex = 2
End If

If Cell.Value = 2 Then
Cell.Interior.ColorIndex = 3
End If

If Cell.Value = 3 Then
Cell.Interior.ColorIndex = 4
End If

If Cell.Value = 4 Then
Cell.Interior.ColorIndex = 5
End If

If Cell.Value <> 1 And Cell.Value <> 2 And Cell.Value <> 3 And Cell.Value <> 4 Then
Cell.Interior.ColorIndex = xlNone
End If

Next
End Sub

[Sa DQ]

Private Sub Worksheet_Change(ByVal Target As Range)

 On Error Resume Next
 If Not Intersect(Target, Range("C6:C1000")) Is Nothing Then
     Target.Interior.ColorIndex = Target.Value + 1
 End If

End Sub 

[zbor]

Kosh, I did it now for you but in future please use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[watersev]

hi kosh, if I understood you correctly you need to have as a result coloured cells having value >0 and each cell within each row from 1 to 55 must have different colour. Please check sample file if it's of any help to you/ Press Run button. That's only colouring part with no condition set for C column.

[kosh]

Hi sorry actually I need the colour to fill into the row not the column.

eg if cell C6 is value 2 then rows V6:BX6 need to be filled in the unique background colour corresponding to the 2 value missing any cells in between cells V6 to BX 6 which are blank or 0

[watersev]

please check attachment, press Run button

[kosh]

Thank you so much!!! Thats perfect!

[kosh]

Hi I have a quick question about trying to extend this range of 55 to 200 rows of individual colours. I was wondering how this would be possible seeing as Excel only has 56 colour index values...many Thanks

[Pepe Le Mokko]

What do you need so many colors for ? Spreadsheets are not really designed for imitating Picasso !

[watersev]

the problem that you may encounter is that you might have equal color to your eye but in reality it is of different shade in red or whatever