Conditional Formatting of multiple lines based on variable?

[bwinchell]

Hello,
I am a newbie to doing anything beyond the basics in Excel so you might have to very specific in explanations. unfortunately I do not know VB (some Javascript & bash) and afraid what I am looking for will require it.
Thanks

Environment: Excel 2013

Current Workbook layout:

	A	B	C	D	E	F	G
1	System	Voltage	RU	BTU	110v	208v	KG
2	computer1	208	2	1945	2.7	1.2	17
3
4	computer2	208	1	780	1.4	.7	7
5	computer3	208	3	1000	3.0	1.5	11
6
7

<tbody>
</tbody>

Explanation of columns:
A = a system name based on a dynamic list created from a tab called "master" > tbl_master
B = the parameter used to search against the object in the tbl_master
C - G = are filled via an INDEX formula against the tbl_master using the system name as the key


Final Result (what I am trying to accomplish [---- represents highlight or something similar]):

	A	B	C	D	E	F	G
1	System	Voltage	RU	BTU	110v	208v	KG
2	computer1	208	2	1945	2.7	1.2	17
3	----	----	----	----	----	----	----
4	computer2	208	1	780	1.4	.7	7
5	computer3	208	3	1000	3.0	1.5	11
6	----	----	----	----	----	----	----
7	----	----	----	----	----	----	----

<tbody>
</tbody>

Explanation of "----" in rows 3, 6,7:

• column C represents the amount of "U" in a computer rack
  • Using the value in C, I would like to automatically fill in the rows with conditional format or symbols (something to indicate the blank space is actually occupied)
  • E.g.
    • if C = 1, no fill necessary
    • if C = 2, fill in the row below with yellow
    • if C = 3, fill in the next 2 rows below
    • etc.....

Any assistance is appreciated.
Thanks
B

 

[bwinchell]

This formula seems to work just fine and do exactly what I want.

Can you explain the formula so I can understand more.
Thanks

 

[Peter_SSs]

Can you explain the formula so I can understand more.

Writing them is easier than explaining them, but I'll give it a go.

The formula changes depending on what row it is in. Let's look at what it would be in row 7 of my sample in post #9:
=AND(NOT(ISNUMBER($C7)),ROW()-LOOKUP(9.99E+307,$C$1:$C6,ROW($C$1:$C6))<LOOKUP(9.99E+307,$C$1:$C6))

The AND, means both the red and blue sections must be true to format the row.
The red part says that whatever is in column C must not be a number. That is True for row 7
The underlined section of the blue part firstly finds the last number in the range C1:C6 (that is the 3) and then returns the row number where it found that value (row 5). So that blue part becomes
ROW()-5<LOOKUP(9.99E+307,$C$1:$C6)
ROW() returns the row number that the formula is in (7) and the LOOKUP() part again returns the last number in the range C1:C6 (3)
So now we have
7-5<3 which is also True for row 7
Both True therefore row 7 is highlighted.

 

[bwinchell]

Thanks for all your assistance.

Attached RackCalculator_4.0.1 is a template of the computer rack elevation calculator I was working on. Hopefully this might help someone else.

1. Master sheet = fill in fields with your equipment details
2. M3-c1 to M3-c5 = the actual 42U computer racks. Choose the device in column B that occupies that U (the list is dynamically built from the entries in the Master sheet)
3. Comp_Racks = gives you an overall view of your racks (space, power, cooling, and cost)

B

 

[Peter_SSs]

Thanks for all your assistance.

You're welcome.