Auto-format a spreadsheet

[phbryan]

Hello,

I'm looking to create a spreadsheet that auto formats as i enter data. For example, based on the values of cells in column "B", fill cells in column "A" a certain color and based on cell values in column C fill cells A to S a certain color. I have items in different rows and based upon what that item is i want certain parts of the row formatted a certain way. I also want this formatting to be active and automatic so that rows format as i enter data as well as format if i need to insert a row between two existing rows.

What would be the best approach? Conditional formatting, macros, VBA?

Thanks

[GeneralDisarray]

Conditional formatting is best for Text Color and Fill color. There are limits to what it can do but it's probably the best way to do. What where you looking to do? Please upload an example with you reply.

[TMS]

I'd suggest you set the data input sheet as a Table. Then formulae, formatting, conditional formatting and data validation will be automatically set up for each new row. You need Conditional Formatting to format cells based on the content of other cells.


Regards, TMS

[phbryan]

Thank you for your responses,

I am using conditional formatting, so far so good.
I have another issue, some rows i want to be automatically hidden based on values in one column. Is there a way to do this?

[TMS]

You can't use CF to hide a row or column, only to format calls, at least AFAIK.

You would probably need a VBA Worksheet Change Event to monitor the column. However, if the column has formulae, you'd need to monitor the cells the formula depends on.

Regards, TMS

[phbryan]

I am almost done,

I am at the point where i want to hide a row based on whether or not is says "HIDE" or "DONT HIDE" in the Z column(Z6:Z500)

Suggestions?

[TMS]

Is the text typed in manually, selected from a Data Validation List, or the result of a formula?

It would be helpful if you uploaded a sample workbook so that any potential solution can be checked.

Regards, TMS

[phbryan]

The text is the result of a formula.
An IF statement that returns either HIDE or DONT HIDE.
It is a spreadsheet for work, so i am unsure if i am able to upload. I have it working now using the filter tool under the data tab. Would VBA code work better?

[TMS]

Would VBA code work better?

I wouldn't think so. Probably best not to introduce the complexity of VBA if you don't need it.

I guess, with VBA, you could make the rows "vanish" as a result of the formula changing. But, obviously, that would require any user of the workbook to enable macros.

Regards, TMS