Excel - Conditional Formating?

  Legslip 09:05 09 Sep 2008

Does anyone one know if it is possible to use conditional formating in such a way that if I enter a sum or word in one cell, then I can designate a group of cells to be formatted accordingly?
i.e if for instance cell A5=10, then cells A1 to A5 are filled in yellow.

  Rockarch 09:10 09 Sep 2008

I don't know a direct way to do this but you could get the result you're looking for by using something like =A5 in each of your A1 to A5 cells then conditional format your A1 to A5 cells to be yellow if each cell = 10 . Would that do the trick?

  Rockarch 09:11 09 Sep 2008

Sorry - you only want the formula in A1 to A4 obvioulsy not in A5.

  Picklefactory 10:21 09 Sep 2008

Yes you can. What version of Excel do you have?
In Excel 2007:
Select cells A1:A5
Select Conditional Formatting on 'Home' tab
Select 'Use a formula to determine which cells to format'
Move cursor to inside formula entry box
Select cell A5 on sheet (Just click on it) and "=$A$5" should appear in formula box
Type "=10" (Without " symbols, obviously)
Select your format via the 'Format' button
Click OK for selected format
Click OK again to accept all and you should be away.

  Picklefactory 10:23 09 Sep 2008

If you don't have 2007, it's essentially the same, but the dialogue boxes are laid out a little differently.

  VoG II 12:01 09 Sep 2008

Tutorial click here

  VoG II 12:01 09 Sep 2008

Tutorial click here

  Legslip 12:12 09 Sep 2008

....but praps I should have explained what I am trying to do a little more clearly. I am trying to monitor a fleet of about 100 vehicles and the last column (F) of my spreadsheet defines the vehicle status i.e. active, or spare or OOS (Out of Service).
I then want the row up to and including my last column(F) to be coloured to define the status i.e if the vehicle is active for the cell enties to be in green. If the vehicle is OOS for the row to show in red etc.

This is what I am trying to 'conditional format' in Excel 2003.

Any further assistance please?

  bstb3 12:34 09 Sep 2008

From Excel 97, but the principle will be the same I hope

assuming the list starts in row 1:

1. highlight cells A1 to F1;
2. select conditional formatting from the format menu;
3. in the dialog box, select 'Formula is' Type =$F1="OOS";
4. Select the cell formatting as required using the format button;
5. Click Add>>
6. in the new section that pops up, select 'Formula is' and type =$F1="Active"
7. Select the cell formatting as required;

8. repeat steps 5-7 for each additional status, changing "Active" for the text of the status and choosing the appropriate formatting.

9. Copy cells A1 to E1 and paste special formats over the range of the list (only in columns A to E)

Job done

  bstb3 12:35 09 Sep 2008

Step 9 should read cells A1 to F1 and columns A to F

  Legslip 14:15 09 Sep 2008

Cracked it!!! (with your help).

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Microsoft Paint set to die after 32 years

Mac power user tips and hidden tricks

Comment désactiver la saisie intuitive et paramétrer votre clavier ?