Conditional Formatting more than 3...

[lalcanta]

Hi All,

I need some help on how to accomplish the following:
I would like to assign a background color to Cell G7 based on a condition from Cell F7…
Normally I would do this with the Conditional Formatting, but I can only enter the formula 3 times due to Excel limitation under Conditional Formatting….

I need to select up to 10 different colors
Example;

=IF(F7,1) then G7 background turns red
=IF(F7,2) then G7 background turns green
=If(F7,3) then G7 background turns yellow

and so on at least up to 10.

Any help would be greatly appreciated.

Best Regards.

 

[Yogi Anand]

Hi lalcanta:

You can use the following code in the Worksheet_Change event

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Number
Number = [f7]
Select Case Number
Case Is = 1
Worksheets("sheet3").Range("G7").Interior.ColorIndex = 5 ' interior.ColorIndex=5
Case Is = 2
Worksheets("sheet3").Range("G7").Interior.ColorIndex = 6
Case Is = 3
Worksheets("sheet3").Range("G7").Interior.ColorIndex = 3
Case Is = 4
Worksheets("sheet3").Range("G7").Interior.ColorIndex = 4
' and so on
Case Else
Worksheets("sheet3").Range("G7").Interior.ColorIndex = 2 ' white
End Select
End Sub

This code may not be elegant ... but it does the job.

Regards!

Yogi
This message was edited by Yogi Anand on 2002-10-07 16:16

 

[winrz]

Would either of you know how to do something a little different than that?
What I have is a rough Gantt chart (think Microsoft Project) with the dates driving the appropriate length of the colored date 'bars'. say one bar extends from J6 to P6 - what I would like to do is place the # of weeks inside the bar at N6. My formula to find the # of weeks from two dates is:
=ROUNDUP(DAYS360($F8,$G8)/7,0)&" " (the " " at the end serve to bump the result over to the left a bit to fit within cell - its cludge but it works for this purpose). I have manually placed this formula in the second to the last cell of each bar so that they look right justified. but since I manually placed them there, any date changes cause the bars to move/expand/contract which changes the placement of the # of weeks. is there a formula i could use that would identify the end of the auto format bar, jump back two cells and place the # of weeks formula within?

 

[Yogi Anand]

Hi winrz:

Welcome to the Board!

You have a good description -- however it will help to see it depicted -- you can use Colo's utility (see link below for downloading) to post sample data.

Also, I believe your question is different enough from this thread that you might want to start a new thread.

Regards!

Yogi