I have an Excel worksheet with a column of text which is data, and some rules in mind for what text values should be formatted with what font and font size.
Cell A1: "Text"
Cells A2 .. (Col. A): strings of text (from a few words to a few sentences) to be optionally formatted with applicable font and font size
Cell B1: "Type of Text"
Cells B2 ... (Col. B): single-word strings such as "heading", "requirement", "note"
Rules in mind:
If string in Col. B is "heading", make string in Col. A font size 18
If string in Col. B is "requirement", make string in Col. A font Calibri
If string in Col. B is "note", make string in Col. A style "italic"
Excel, as a spreadsheet, is all about functional programming (formulas) and parallel processing (recalculation). Yes I can write procedural VBA but, for me it goes against the spirit of spreadsheets (even though procedural programming is so popular that Excel and predecessors have offered that for a long time).
Any ideas on how to do it other than special-purpose VBA procedural programming?
What does not seem to solve
Excel conditional formatting does not solve because it expressly does not allow setting those particular cell attributes.
Excel UDFs (User Defined Functions) do not solve because they do not affect other cells. As expected for functions. And even if I wanted to make a function which duplicates the unformatted text but with a style or size based on value, UDFs do not affect the "environment" - only can return data.
And I found here that I can't even put VBA code as text in Excel worksheet cells and then evaluate the text as VBA -- to make a simple generic VBA procedural processing engine for font style and size VBA statements that I would put in cells in my worksheet.
(If any of this negative information is incorrect, and one of the above will work, please correct me.)
VBA special-purpose, procedural programming example -- what works but trying to avoid
Public Sub IterateThroughRangeSetFontStyleSize() Dim wb As Workbook Dim ws As Worksheet Dim rng As Range Dim cell As Range Set wb = Application.ActiveWorkbook Set ws = wb.Worksheets("myWorkSheet") Set rng = ws.Range("b2", "b4") For Each cell In rng.Cells If LCase(cell.Text) = "bold" Then cell.Offset(0, -1).Font.Bold = True If LCase(cell.Text) = "italic" Then cell.Offset(0, -1).Font.Italic = True If LCase(cell.Text) = "large" Then cell.Offset(0, -1).Font.Size = 18 Next cell End Sub
Some ideas that seem too difficult to me but maybe someone will say how they are not
Configure Excel to allow VBA to write to VBA editor (VBE), read the worksheet column of data, calculate desired font style and size based on the rules, and autogenerate VBA procedural code to then update the font style and size of the column of data
Export or Save As the Excel file as XML, write an XSL style sheet that would format per the rules, open (import?) the XML and tell Excel to use the XSL style sheet
Some outside-the-box, keep-it-simple thinking!
It could be possible to keep data and formatting information separately:
Sheet 1: Data;
Sheet 2: Function describing the cell formatting for a corresponding cell on Sheet 1 (resulting in bgcolor="#ff00ff", font size="3" etc.);
Sheet 3: Data and format merged, giving XSL.
The only VBA needed is to write one UDF for Sheet 2 and the other one for Sheet 3.