Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(80% Discount Ends Soon!)

Formatting Text in Excel

Ok I have been attempting to do this for sometime and am at a complete loss.

What I am doing is checking dates, first off the day of the week on a cell above, then I compare it with the due date entered to the right. When the date due is matched up with the day of the week it will change the cell color to red, this was the easy part.

What I also want to do BUT can't is to add a Bold, white letter D at the same time.

For example.

_ |_D__|_E__|_F__|_G__|_H__|_I______|
5 |Jan1|Jan2_|Jan3|Jan4_|Jan5|Date due_|
6 |____|____|__D_|____|____|__jan3___|
7 |____|____|____|_D__|____|__jan4___|
8 |_D__|____|____|____|____|__jan1___|
9 |____|__D_|____|____|____|__jan2___|

Does anybody know if this can be done? I am thinking I need to use an IF formula but I don't know how to set it up. If at all possible in the same formula would be awsome.

Thank plenty

Link S

Is there a way to format a text box on a user form to only allow date
entries ie: mm/dd/yy or mm/dd/yyyy?

I would like Excell to treat all of the cells as text even if they contain
numbers, all the time. Is there some way to do this? I am interested in
things like ($67,568) being changed to -$67,568 and things of this nature, I
am using test to columns and it is always trying to change the format, so I
would liek to be able to nit have to click through each column and change its
format back to text. in reality my bigger probelm is I would like to create a
document in which none of the content can be modified only the formatting.

Is it possible to change the color of text using a formula, such as an IF
statement? What I have in mind is something like this (stated simply): If
cells B9 or N9 or Z9 or AL9 are blank, do nothing, else color text in cell S4

How do I format a text box?

Hello everyone. I'm having a bit of a problem with color formatting in Excel 2003 lately.

I did a search for this topic, but all I found were references to "searching" for cells colored a certain way, or sum/average/locate cells which were colored. I couldn't find anything that said something about changing the actual text itself based on a set of criteria.

My situation is this...

In cell A1, I have a drop-down list from Data Validation. When a specific selection is made in the list, cell B1 pops-up with a text entry based on a VLOOKUP which checks cell A1. For simplicity sake, I will use simple letters for these text entries. For example, if the 1st item in the drop-down is selected, the letter "A" will appear in cell B1 and I need it to be RED. If I pick the 2nd item in the drop-down, I need the letter "B" to appear and be BLUE. This continues for 6 different entries, all of which being a different text and color at the same time. If I go back and change the drop-down selection, I need the color of that text to change accordingly. I've actually got close to 30 consecutive rows worth of Drop-Down lists, each one corresponding to cell in the next column. I could really care less what the colors actually are, as long as they are readable on a white background. In fact, if the font colors themselves are the problem, I'll even go with changing the background at this point.

I've tried Conditional Formatting already. The 3 limit doesn't allow me to do what I need to do, which is to have 6 different colors used for the text that is inside the cell. I'm aware of the Format > Cells > Number Tab for Custom selections, but that only seems to work for text in general, not for specific text. Since the entries in B1 are not numbers, I can't use the built-in [Red] and [Blue] etc. for the custom formats. If anyone knows of a way I can specify a color based on specific text entries, please let me know. Perhaps a combination of Conditional Formatting 3 of those colors and something else entirely different can solve the problem.

I already have my lookup function in cell B1, but if there is some other color-based function that I can add to it...that would be great. Something like: IF(A1=1,"Text is Red",IF(A1=2,"Text is Blue"... all the way to 6. I know I can fit up to 7 IF statements, so that wouldn't be a problem, but I haven't found anything anywhere which has this.

Basically, I need a way to automatically change the text 1 of 6 colors based on the specific text entry. I would be more than happy to explain this situation in further detail, but I think I've gone on far enough as it is. Thanks to anyone who can offer assistance.


I have various text documents that I've pasted within Excel.

On some occasions the entire document fits inside a single cell, in other cases the document overlaps several rows (dividing each sentence to its own row/cell )
I understand its a formatting issue, however I'm trying to duplicate this within excel.

If I type a large body of text( a few paragraphs of common sentences) within a single cell and paste this into another cell. How can I format this such that it fills into the lower rows (ie: a new row per sentence)

Thanks for any help


Is it possible to do a conditional formatting command on a Text cell, with the condition that:

if the cell contains text "searchterm"

then the conditional format takes place?

Hi folks, Thanks for taking the time to look and help out!

I have data coming from an SQL db and some of the fields of my report have been formatted via VBA to do things like convert email strings into actual email links, and some of the data is just plain text.

My problem is that one of the fields has imbedded HTML codes that were created by the original app and stored within that field in the SQL db. Only some of the records have the formatting but when they do, it is typically for text formatting, but occassionally to imbed links. I need to have Excel interpret these HTML codes in this one field back to the text formatting and links that they represent.

Here is an example:
<html><body>Folder to contain requirments <b>rejected</b> because they are duplicates.</body></html>

How to I get Excel 2003 (or 2007 if necessary) to convert the HTML?
If it can be "converted", can Excel interpret or view the HTML? If not, can it be stripped out?

I have looked at this in Excel 2007 and it does not automatically interpret the HTML. I have also tried exporting the whole spreadsheet to Web Page and then doing a Data/Import as per the suggestion for a similar problem someone posted. This did not work.

Thanks in advance for your ideas!

I would like to have cells in a certain column turn green if the word in column J of the corresponding row = Not Changed"


Dear All,
I'm having a workbook with plenty of words on c' columns.
BR-123,BR-124,BR-125 like this,just i need to give a conditional
format based on it should be of same space.

Eg:BR-123 & BR-124 are similar ,but it cannot be Br-125 or BR - 126.

Please advise,

Thanks in Advance,

For multiple cells in a fairly large spreadsheet, I would like to use conditional formatting (e.g. apply a red highlighted background) if the cell itself is equal to any of about 20 potential words (examples of these words are "unknown" or "N/A" or "not computed" or "score not calculated" or "Not reported", etc.). I would want the conditional statement to say something like, "If cell is equal to = "unknown" or "N/A" or "not computed" or "not reported" then turn cell red.

This is a data entry page that my data entry staff will be entering data into based on preset dropdown lists that I've already set. I just want to the file to automatically highlight in red the cells in which my data entry staff entered the specific words listed above, so that I can quickly scan the document and discuss the "red" cells with my data entry staff.

I'm building an invoice template for my own use (self employed trade). I
want a cell to be a properly formated 2-letter state abbreviation in caps no
matter how I enter it. I suppose that will involving limiting the length ot
the text to 2 letters and performing some action to capitalize the text if
necessary. I don't know if the best way to do this is with a macro or some
other way. I think I would prefer a macro to automatically capitalize all
letters in the cell after the text is entered no matter how exited (enter,
tab, mouse click).
I'll settle for the capitalization if the 2-letter limit is too hard.
I know I could do a data validation list, but I'd rather not.



I am putting a cover sheet on a spreadsheet I am putting together and I have a couple of questions I was hoping somebody could help me with.

First I am having trouble formatting the text in the box, I can't justify the text I can just pick left, right or centre aligned. Is there any way I can get it justified?

The second and more important part of the query regards hyperlinks within text boxes. I have two bullet points in the cover sheet and would like to insert a hyperlink in each line, the first to sheet 2 and the second to sheet 3. Something like the below

" > For info on Subject A, please see Sheet 2
> For info on Subject B, please see Sheet3 "

Is there a way I can do this where by the bold parts in the quotations are separate hyperlinks as so far my attempts to make hyperlinks in the text box have resulted in the whole text box becoming one big hyperlink.

All suggestions are welcome and thank you in advance for your help.


Right guys,

Having some trouble using conditional formatting.
I have a workbook of about 4000 rows of data.
I am using lookups to pull relevant data onto one sheet to display it in an easier to use manor.

Some of the values are coming up as #N/A, but this was expected.

I am basically trying to use conditional formatting, or something of that kind to replace the "#N/A" error with the word "Solid".

Any help would be greatly appreciated.



What do I need to do to use the concatenate function to take two cells of data:

A1 = 42
B1 = 13.56

to get this result 42O13.56 ... where O is superscripted to indicate degrees?
I have the basics ...


, but how would I format the "O" to be superscript?


I'm trying to find a number of phrases in a document and colour them according to which phrase.

Using macro recorder, I've made the following but I'm wondering if there's a clear way of writing the code so more phrases can be added at a later date.

Sub ColourTextMulti()
Selection.Find.Replacement.Font.Color = wdColorBlue
With Selection.Find
.Text = "Phrase One"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With

Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Replacement.Font.Color = wdColorRed
With Selection.Find
.Text = "Phrase Two"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
End Sub

Could this be written in a form that listed

"Phrase One" = wdColorRed
"Phrase Two" = wdColorBlue


I originally posted the following in the General Forum:

I would like to use the 'Conditional Formatting' function on a text box. I would like for the text box on this Sheet2 to change colors when I've typed something into Sheet 1. Can conditional formatting be used on text boxes? If not, is there another way to tell Excel what I am trying to do?

Reply to my question:

teylyn -
'You can't use conditional formatting on a text box. To dynamically manipulate the textbox colors you would need to use VBA.

Start a new thread in the Programming forum if you want to pursue that option.'

New Question:

So I guess I can't use conditional formatting on a text box. Can someone help me with VBA. I've never used this and don't know where to start.



I have spent ages searching for an answer and I suspect this may not be possible to achieve in excel, but here goes...

I have a group of cells that are conditionally formatted using a formula to apply one of three colours depending on which condition is met (NB, the cells don't contain any text or numbers). See attached sample file.

I would like to add some text to the conditionally formatted cells to display a dynamically updated text string.

Question 1
Is there anyway to achieve this in excel?

Question 2
Is there any way in excel to allow a number to run over into adjoining cells in the same way that text does if there is no entry in the adjoining cell?

I would be grateful for any help on this... as to if it is possible to achieve.

Many thanks.

from cell C1 to cell C39 i have a list of roles (which i hide), below other people sign in and in colum C is where they enter there role, sometimes people enter their role in incorrectly, If this occurs i want the cell to highlist red, ive checked the Conditional Formatting > manage rules > new rule > format cells that contain,

I have cell value not equal to, but dont know what to enter in that box to do what i want,

The contents of the cells C1:C39 are all text, i would also want the cells not to highllght if it is blank

thanks in advance for any feedback

I have the text below in a embedded formula in a merged cell and want to apply text formatting to certain words within the cell. Is there a way to format a single word as italics?

="Hamilton Valley Management, Inc. provides bookkeeping and required reporting to the USDA - Rural Development on a monthly basis. Hamilton Valley Management, Inc. was paid $"&TEXT('Note E-G'!$D$5,"#,##0")&" and $"&TEXT('Note E-G'!$I$5,"#,##0") &" for the years ended December 31, "&(WTB!$A$1)& " and "&(WTB!$A$2)&", respectively.

I'm wondering if there's a way in which I can change the colour/shade and font colour of a text box in Excel when a particular cell displays specific values.

For instance, if cell "P5" displays "R" I would like text box 1 to display a shade of red with the contents in bold white font. If "P5" displays "A" it needs to be amber with regular black font, if "P5" displays "G" it needs to be green with regular black font.

I'm happy to use VBA, in fact I would say this is the only option.

If you need any further clarity, I'd be happy to elaborate.

Many thanks.

Hi all,

Pretty sure this is not possible (I've googled it and found nothing so far) but I am trying to create a formula in Excel 2007 that allows me to markup the output a little like HTML to get bold/italic text – an example would be something like this:

="This is my first name in bold text:< B>" & B2 & "</ B>and this is my last name in italics< i>" & B3 & "</ i>"

(NB: I had to put spaces in the tags to stop the forum engine rendering them as HTML.)

This is a very simplified example - I am trying to develop test cases for a project I'm on and the output format I'm trying to reproduce automatically has quite a lot of sort of formatting with each formula referencing values from 10-20 other cells that need to be in bold/italic.

So is it possible to do anything like this in Excel 2007?

TIA for any suggestions... I have 100's of test cases to write and I want to automate it as much as possible!


Thanks in advance for any help from the board!

I have a sheet that tracks wether a tasks in a column have been completed or not by a drop down menu with "Yes" or "No" as the options. If "No" then the cell turns red, if "Yes" or blank it stays un-filled; that parts easy.

What i can't figure out is the column for the date of completion; I can get the cells to format to a specific color based on the date range i want but i can't figure out how to keep the cell un-filled if there is no date at all.

I would like to be able to only conditionally format a cell (A2) if the name cell (A1) has a name in it, if not the cell remains unformatted.

Is this possible?


I want to enter a short (< 10 words) text string in vertical format in a column. The problem is that I want the text to spill into adjacent cells, vertically, the way text would normally do horizontally. I do NOT want the cell heighth (i.e. row heighth) to be re-sized NOR do want to to merge cells. When I go to "Cells / Format / Format Cells / Alignment", I can easily change the text orientation to vertical but it then resizes the heighth of the cell (row) to accomodate rather than allowing the text string to spill into adjacent cells as it would normally do when the text is in horizontal orientation. Excel 2007.

Hi all,

I am very new to the Macro and VBA "underworld" of Excel, and I have a (hopefully) simple procedural formatting task that I believe can be automated; I'm just not sure how.

The short way to explain what I need done is to refer you to the attached worksheet and say that I would like to write code to have excel apply formatting to the "raw" worksheet and make it look like the "formatted" worksheet.

The long way is to provide a detailed explanation of the 3 specific subgoals to this general formatting task:

1a) Find all cells containing the trial name (e.g., "Trial 2 Interruption nil") and 1b) Highlight all cells on the same row yellow

2a) Find all cells signaling the beginning of an interruption (e.g., "Interruption TQ begins"),
2b) Find all cells signaling the end of an interruption (e.g., "Interruption ends"), and
2c) Highlight all cells on the same rows as 2a and 2b and all cells on rows in between 2a and 2b light yellow

3a) Find all cells in column F containing the word "incorrect" and
3b) Highlight those cells red

The purpose of this reformatting task is to make a range of datasets more amenable to coding and analysis. Any help would be greatly appreciated.

Can it be done?

I'd like to have two lines, both centered, and the second one bold.

How can I do that....?

hi all,

in conditional formatting methods there is one "format-cells-based-on-their-values". this sets a background color in a continuous tone. (ex: red to green).

the problem is it says nothing of the foreground i.e. text color. Due to this the cells having a dark shade get tough to read.

does anyone has a solution for this. how can we set the foreground color depending on background color, possibly in a similar continuous tone. this will make a good contrast.

thanks in advance

Hi to all,

I know that you can highlight a cell or it's entire contents with a certain color, but can you highlight only certain portions of it's contents using Conditional Formatting?

A simple example: If cell A1 contains the text "The pink elephant has blue and green spots." and cell B1 contains the text "blue", using cell B1 as a reference, is there a way to highlight only the text "blue" in A1 with the color blue using Conditional Formatting?

Thanks in advance for any answer to this.


I have a file spooling from Oracle to a format that opens in Excel. Some part numbers are coming in as numbers in advance and then formatting as scientific notation. That's bad enought, but when I format them as text, they stay in that format even theough the cell contents are correct. I can't control the format from the Oracle side, but was wondering what I could do with Excel. I thought maybe there was a way to set the default to text or something in advance, or maybe to develop a VBA routine. In the meantime, I may have to tell the user to do something. Does anyone have any suggestions?

I have an Excel 2003 list which has cells containing only text. How do I format each "paragraph" within the cell to provide spacing before and after the paragraph (as is done in Word).

So far my only solution is to Alt-Enter to manually add space before and after, but I want to specify the paragraph formatting like can be done in Word.

Thanks, Reb

I'm using Excel to track audit findings and am having trouble with a few cells. I'm using Excel rather than Word so that I can filter the findings. There is a large amount of text in the cells (1900+ characters) and it uses 18 lines of the formula bar. I've started a new line in the cell by using Alt+Enter several times as well.

Here's my problem: I have wrap text turned on but only the first 12 lines wrap. I'm using Excel 2003, but when the same file is viewed in Excel 2007, it displays perfectly. Any ideas?

Hi everyone,

I'd like to highlight a row if the text in two cells does not match. For example, if cell D4 = "DC" and cell E4 = "DC", I'd like it to remain with no color. If cell D5 = "DC" and cell E5 = "CA," I'd like all of Row 5 to be highlighted. Can anyone help with this?

I'd like to apply this conditional formatting to the entire worksheet.


I'n trying to work out a formula for Conditional Formatting a row to format the text BOLD if it contains a certain phrase or name within the cell.

So far i have made a list of the names i need, named the range "LG" and used the following code:



However, this is too specific, it only picks up exact matches, my criteria of names will change constantly but will always have the constants of RBS, RBOS or Natwest contained within the cell, is there an alternative to MATCH that i can use that is less specific?


Hello all
I use conditional formatting a lot, especially for text content. However, I find it frustrating that it takes so long to write the formatting rules. Suppose I have cells which can have any of these values:
Intermediate II

...in order for the different categories to show in different colours, I have to write five rules. What I really wish I could do is have another tab with just the five unique entries, format them as I'd like them, and then have a conditional formatting *look this up*.
I could have sworn I'd seen a demo once for something like this, but after fruitlessly searching the help menu, I figure I must have confused it with something else...

Is there any way to speed up writing conditional formatting rules?

Hi All me again,
Got the stats working great thanks to the advice from those who replied.

I now want to apply conditional formating that will not occur if the cell value is "G", "F", "/".

I've got it working fine with numerical values that are not in range of the result of statistical testing. However the cells that have G, F or / also have the conditional formatting applied and I don't want it to.

Thanks in advance for the helping me.

I hope someone can help because this is driving my crazy...

I have a spreadsheet of costs that is merged to a word document. The columns are all formatted as currency and contain either a numerical value or the word "no". When merging, some of the columns merge correctly and the word no appears but in one column the no's appear as a 0. Reformatting does nothing, the only way I've been able to change it is to use the text or trim function to force everything in the column to text. I then get the no's to appear but I lose the currency formatting. If I change the formatting on only the numbers in the column back to currency it casues the no's to go back to merging as zeros.

Any help would be appreciated!

Hi -

I am losing zeros after the decimal point when I convert currency and percentage numbers to text.


Cell TAM!$AA$37 is $25.00 (number formatted as currency, two decimal places)

Formula to convert to text that has necessary formatting:


Result: $25.

Any ideas on how I can keep my placeholder zeros when converting from number to text?


I have a list of numbers stored as text and I want to conditionally format the column to highlight those text stings longer or shorter than 11 characters... can someone help me out with the formula I need??

In addition to coloring a cell via conditional formatting. Can the cell also be conditionally formatted to return a text message. Normally I would use a "IF" statement, but the cells in question also contain a validation list drop down that wipes out the "IF" statement whenever the selection is changed. I'm looking for the conditional formatting or VBA code to overide the validation list when condition is met.
A1:A2000 contains a validation list.
If B1:B2000 is greater than "0", enter "Done" in adjacent "A" cell.

How can I format a cell to automatically change any entered text to all capital letters?

Thanks, Eric.

I have a column with a mix of numbers and text. I'd like to leave the numbers right justified. How would I conditional format so that if cell has text it is centered?

(e.g. Some cells have '''', no numbers. I was able to change colors in these cells with conditional... could not find how to center.)

I am trying to use the conditional formatting feature (of Excel2002) to highlight cells which contain a text string (in this case a last name) from a drop down list I created.
For example, if I select the name "Monroe" from the drop down list, all the cells in my conditional formatting range that contain only "Monroe" in the cell turn green as I want. (I used the cell-- is equal to command in the conditional formatting box). But when the cell contains more than just "Monroe" such as "Monroe/Smith", the cells do not get highlighted. How do I get the conditional formatting to apply to every cell that contains "Monroe" regardless if there is more in the cell or not?

Thanks for any help with this matter.

Sorry if I am neglegent, but I am a new member to the board and not well versed in VBA.

I want to control the formatting of text that will appear in a message box using Excel 2003 VBA. I want to control the alignment, font, size, etc. For example, I want an OK only message box that reads;


This spreadsheet was created by:

Your Friendly Neigborhood Spiderman

(If you are going to create something worthwhile, you might as well take credit for it.)

Where "Credit..." is the message box title. The following two lines should be centered. The second line in bold and use a larger font size.

Please be detailed in your response as I am fairly new to this.



Ive been googling and using the help pages to try and find out the vba code to allow me to format charts x,y axis titles text direction.

I tried recording the macro and its doesnt generate the full code

Here's what I do in excel :-
Right click on the appropriate x or y axis title within the chart -> Format Axis -> Alignment.

Now from here I would like the vba statements to change the text direction parameter (In this particular case I want to rotate text by 270 degrees).

Can anyone help?
Im using excel 2007 btw


Can I choose to highlight rows based on a cell in that row containing specific text.

For example, I have data in 4 columns. Columns A, B, C, and D.
In column B, there are lists of customers.
I would like to highlight any rows where the customer has the text value "TDMN" somewhere in cell B for that row. It might say, for example, "CUSTOMER TDMN2 CUSTOMERADDRESS", and I would like that row highlighted.
I had been doing this by hand, but there are hundreds of lines, potentially, and it would be much easier to conditionally highlight anything that contains *TDMN*. Is that possible? I tried several variations of that formula in the highlighting and couldn't come up with anything that worked.


I'm looking for the equivalent of Word's paragraph formatting where you can add space before and after a paragraph. In Excel with wrap text turned on, I currently change row height manually to give the same visual appearance but I'm looking for how to automate it. Ideally would like to have row height be 12 points greater than Excel currently makes it.

Good Morning

I have a spread sheet where i would like a range of cells highlighted if a certain cell contains text (using conditional formatting).

So if cell A6 contains text then A6:P6 will automatically have a border put around it. I have found a formula to do this:


However i would like this formula to do the same for multiple rows, e.g. cells A6:A8 all contain text then borders will be placed around each cell between A6:P8, but cell A9 does not contain text, so I do not want A9:P9 to be bordered.

At the moment I can insert the formula for each row, however there are hundreds of rows and this will take a long time to do this.

Is there a simpler way to do this?

Please Login or Register  to view this content.



I want to use traffic lights (less obtrusive than full shading) to highlight these 3 results in a column (range O6:O37 in my spreadsheet), creating an easy to see dashboard.

The traffic light to be in the same cell, not in an adjacent column.

Column Contents i.e. Format Desired Traffic Light
Any date* Number Green
Blank Cell Number/text Amber
"Rejected" Text Red

Attempts/Difficulties so far with "Edit Formatting Rule" box (Conditional Formatting)

a) To set the Conditional formatting I recorded a Macro following my mouse strokes: Conditional Foramtting/Manage Rules/Edit Rules/Edit Formatting Rule box Select a Rule Type" - I selected "format all cells based on their values". Icon set (traffic lights).
Result - This seems to default to Applying the default Top third (green), Middle third (Amber), Bottom third (Red) split to the data they are considering (underlying number which Excel holds for a date). Cells with "Rejected" or just no text (blank) were just ignored .

b) To set the Conditional formatting I recorded the same Macro as above, Select a Rule Type - "format all cells based on their values" Icon set (traffic lights) but changed the "Type" for Textual results (blank) or "Rejected" to "Formula" telling it to apply amber/red respectively when cells contain these texts.
Result - Same as above .

c) To set the Conditional formatting I recorded the same Macro as above, Select a Rule Type - "Use a formula to determine which cells to format" R Result - Works, but doesn't allow trafflic light icon formatting . Only shading.


d) VBA code Attempt Result - Same as above. N B - What does "Operator = 7" mean - which I turned off.

Please help!

My VBA code so far is below:

Usual format, I have highlighted comments/non-functioning code in green

Please Login or Register  to view this content.