Conditional formatting

[Mike]

Can I use conditional formatting in such away that when data in Field 2 = D,
the cell in Field 1 must be date format, when Field 2=H, data in field 1 have
1 decimal point, all other values in Field 2 will set Field 1 to zero decimal
point?

Regards,
Mike

 

[Matt]

Yes. Use a formula in the conditional formatting. For example with

=$C2="D" you can then specify date formatting

Then add another rule with
=$C2="H" and specify 1 decimal place

Use the 'C' column for your H,D, etc values

I'm using Excel 2007 which I just installed this week so I don't know if
this works in Excel 2003.

 

[Matt]

I was trying to past in the spreadsheet cells for you to look at, but it
won't paste properly into this text editor. It looks something like this. I
have 12.345 entered in every row of column B and column C has the alpha
formatting code.

Column B Column C
12.3 H
1/12/1900 D
12 A

Hope this helps. If you leave the $ out of the formula in front of the row,
it will use whatever row you are currently in from column C in the
conditional formatting statement.

 

[Mike]

Hi Matt,

You're right, I'm using Office 2003 and it doesn't allow number formatting
in the conditional format option. It allows font, border and pattern
formatting only.

I did tried your formula with font color formatting, it did worked. It just
that I need to find a way to get the number formatting done properly.

Thanks for your tips though.

 

[Matt]

Mike,

You could also do it with a macro. It's more involved but you can look at
the one column to see what the letter there is and then use the
<b>Selection.NumberFormat = "#,##0.00" </b> command to do formatting in the
other column. With macros you can move around the spreadsheet and do all
sorts of things.

If you're not familiar with macros a good way to get started is to record a
macro and then look at it in the visual basic editor to see what it did.

Matt