Microsoft Office excel 2007 - upgrade

Is there a way to highlight only a group of cells, not the data tied to those cells? For example, I want cells a2:d21 to always be light red, then a22:d70 to be light blue. I want this to be the case regardless of how I sort the data. As it stands now, when I highlight those cells then resort, the highlights always stay with the cells originally selected and highlighted, instead of just staying with a2:d21 and a22:d70. Thanks!

You could use Conditional Formatting to do the job. I’m using Excel 2000 so these directions may be off a bit:

1) Select your cells - A2:D21

2) On the ribbon click Conditional Formatting

3) Click on New Rules, it’s near the bottom of the dialog box.

4) Click Use Formula to determine which cells to format.

5) Enter the formula: =IF(TODAY()<>NOW(),TRUE,FALSE)

6) Click on the Format button

7) Select the Fill Tab

8) Select a pretty color

9) Click OK

10) Click OK

The TODAY() function will refresh the date whenever the worksheet recalculates.

The NOW() function will refresh the date/time value whenever the worksheet recalculates.Since they both recalculate automatically and will never be equal, the conditional formatting will always be true.

There is undoubtedly a VBA solution.

MIKE

That worked! Thank you.

Just as an alternative, you can also use the following formula: But here, you have to select your entire range, "A2:D70".

=IF(AND(ROW()>=2,ROW()<=21,COLUMN()>=1,COLUMN()<=4),TRUE,FALSE)

Other than the Range Selected, and the Formula, follow Mike's solution.

...and because I am not in the mood for work right now, I messed around further.Here is a conditional Format that use two Conditions to determine if the Cells will be Blue or Red.

Select Range "A2:D70".

(I have tested it on "A2:D75".)Condition1:

=IF(AND(ROW()>=2,ROW()<=21,COLUMN()>=1,COLUMN()<=4),TRUE, IF(AND(ROW()>=22,ROW()<=70,COLUMN()>=1,COLUMN()<=4),FALSE,""))=TRUE

Format with a Red Colour.Condition2:

=IF(AND(ROW()>=2,ROW()<=21,COLUMN()>=1,COLUMN()<=4),TRUE, IF(AND(ROW()>=22,ROW()<=70,COLUMN()>=1,COLUMN()<=4),FALSE,""))=FALSE

Format with a Blue Colour.

Note:

The formulas must be on one line.

I just split them to make it easier to read.

Thank you! I feel special that you dedicated your work time to answering my question! :) I appreciate your help.

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History