Conditional Formating a range by a specific cell value

[gminor7]

I have 20 columns and 300 rows. I would like to bold all 5 cells to the
left of whatever cell meets a specific criteria. So if E5 is equal
to "closed" then E1-E4 will all be bold. At the same time if J10 is
also "closed" then J6-J9 need to be bold. I have some Ideas but I
would like some help if any one has any.

Thank you,
Jaime

 

[Dave Peterson]

You wrote all 5 cells to the left of E5 (E1:E4).

This looks like it's only 4 cells and it's above the cell with the criteria.

I'm guessing that you meant all 5 cells--the cell itself and the 4 ABOVE
(E1:E5).

And I'm guessing that the "Closed" goes into rows that are multiple of 5
(5,10,15,...)

If that's true then select your range:

E1:E300 and then hit ctrl and select J1:J300
Now you should have both columns selected
But one more thing (just so we're on the same page), ctrl click on E1
(so both columns are selected, but E1 is the activecell (it's white))

Format|Conditional Formatting...
Formula is:
=OFFSET(e1,MOD(5-ROW(),5),0)="Closed"

E1 should point at the activecell (if you didn't do that extra ctrl-click).

Format like you want (bold).

 

[Earl Kiosterud]

Gmin7,

Your post seems to contradict itself. You say 5 cells to the left, but your
example cites the 4 cells above. For the case of 4 cells to the left, try
this:

Select the cells, and have the active (white) cell in A1 (adjust the cell
references in the following formula for another active cell).

Format - Conditional format - Formula is:
=SUMPRODUCT((B1:E1="closed")*1)

Note that the formula refers to the 4 cells to the right of A1 (for 4 cells
to the left). Adjust as necessary.

Earl Kiosterud
Mvpearl omitthisword at verizon period net.

 

[gminor7]

Sorry your right I did contradict myself. I do mean to the left of the
cell.
Let me correct my example.

If E5 = "Closed" then A5-E5 need to be bold at the same time if
J7="Closed" then F7-J7 need to be bold.

Thanks,

 

[Dave Peterson]

Highlight A1:E###
(make sure row one has the activecell)
Format|conditional|formatting...
Formula is:
=$E1="closed"
(apply the bold format)

Highlight F1:J###
(make sure row one has the activecell)
Format|conditional|formatting...
Formula is:
=$J1="closed"
(apply the bold format)