Conditional Formatting more than 3...

lalcanta

New Member
Joined
Sep 27, 2002
Messages
11
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.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi lalcanta:

You can use the following code in the Worksheet_Change event

Code:
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

Board Regular
Joined
Sep 4, 2002
Messages
106
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
 

Forum statistics

Threads
1,144,329
Messages
5,723,737
Members
422,512
Latest member
MHau5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top