Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Formatting Cell Color To Specific Text


I need to be able to assign colors to cells depending on what text is inputted.
If a name start with letters between:
A - Cald (I want the cell to be Yellow)
Call - Eg (I want the cell to be Black)
Ek - Hall (I want the cell to be Red)
Etc.....


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Difficulty Writing Code For Changing Font Color If Cell Contains Specific Text
I have a spreadsheet that will contain about 5-15 rows with a letter "S" in the column. If this letter S appears in the column, I need its entire row to change font color to RED and then change that row's value in column L to a negative number. is there any easy way to do this?

View Replies!   View Related
Conditional Formatting - Finding Text Color
I have a workbook with 4 sheets. Each sheet is set up the same. There are 4 columns. The column headings are Title, Item #, 2009 Retail Price, and Price changed from 2008 (in A, B, C and D respectfully).

I need to find all red text in column C and place a 'Y' in column D. If the text in column C is not red I want a 'N' in column D.

View Replies!   View Related
Conditional Formatting Specific Text Containing ONLY
I have a conditional formatting rule that checks for misspellings of client names.

My employees input client names into a spreadsheet, and I need for the spreadsheet to notify them via a fill color if they have misspelled the name.

So, I set up a rule for each client name using the specific text criteria.

The rule simply checks for the client's name, and fills the cell green if the client's name is correct. If the client's name is misspelled, then the cell remains unfilled.

This works great if the client's name is spelled correctly, or spelled incorrectly.

But there's one hitch:

If the client's name is spelled correctly, but there is some extra character(s) inputted after the client's name, the cell is still filled green even though the data in the cell is useless because the extra character will cause it to be rejected by the next party down the line in the value chain.

So I'm hoping to find a way to make the specific text containing criteria operate with only the specific text I specify and no extra characters or anything after the specific text.


View Replies!   View Related
Color Cell Fonts Based On Text Color Of TextBox Controls On UserForm
I have got a userform with lots of controls,

One of the action's on a large group of the controls is the same but except for one number

here is an example

If TextBox107.ForeColor = 255 Then ActiveCell. Offset(0, 53).Font.ColorIndex = 3
If TextBox108.ForeColor = 255 Then ActiveCell.Offset(0, 54).Font.ColorIndex = 3
If TextBox109.ForeColor = 255 Then ActiveCell.Offset(0, 55).Font.ColorIndex = 3

This makes a cell that correlates to the textbox red if the text in the textbox is red.

Now, I loads of these textboxes that all need to run the same code with just the Offset value one digit higher than the last and I was hoping I could create a loop to avoid a huge block of code but I can't work out how to make a constant that will +1 with each loop.

Also, can I assume that a loop will start with the control with the lowest number i.e. Textbox1 and then work its way through the rest of them in order?

View Replies!   View Related
Change Text Color Based On Cell Color
I have various row cells in column (F) filled with the color Green. And corresponding text in Column G. How can I change the text of that particular row to white.

i.e.: if any cell in column F is Green, change the text color of that row in Column G to white?

View Replies!   View Related
VB Color Formatting More Than 4 Cell Conditions
The code below turns numbers and dates on my worksheets(1) to text priorities on my worksheets(3), I now need to color the cell backgrounds of the results on worksheets(3). With formulas I know I can conditional format up to 3 colors (or 4 utilising the background) but I am after 6. I also know that you will look at my code and laugh as I should have done the color coding with the initial commands, but I struggled with it for 2 days and simply couldn't get it to work.

In the VB below it all works off a command button and up to the third color it works however it then gets stuck and throws up a runtime error '1004' Application defined or object defined error. I did get around this before by recording macros and effectively sticking them together but it hasn't worked on this occasion...... any ideas??? (I hope I'm not breaking any rules by pasting the code directly in?)

View Replies!   View Related
Conditional Formatting To Color Cell
I am using the following formula on a conditional formatting to color cell A1 based on cell N5 date =$N5=TODAY()-2

The problem is that I need this formula to consider only weekdays, how can apply formula weekdays to my formula?

View Replies!   View Related
Conditional Formatting Cell Color
I am trying to write a VBA code to color cells.

In Column I

I need a formula to change the color of the cell based upon the following criteria

In cell I3, if J3>7 change color to teal, if H3<$A$1 color cell red, if J3<7 color cell yellow, if J3="COMPLETE" color cell grey, otherwise do nothing.

View Replies!   View Related
Color Row If Cell In Column Contains Specific Word
I've been trying to come up with a conditional that will format a row if a cell contains any instance of a given word.

I know the following will format rows containing the EXACT entry of "apple" in column E:
=$E1="apple"

But I'd like a conditional that will find entries like "apple pie" and "crabapple" as well. I'm sure it's a simple solution, using a SEARCH function or something...but I can't figure it out.

View Replies!   View Related
Find Cell With Specific Fill Color
I trying to look for a all the cells with the same fill color in the same column and assign a specific text to the cell for example

Column A - Cells with fill Yellow must have for text the word Sub- Total
Column A - Cells with fill Black must have for text the word Grand Total

View Replies!   View Related
Conditional Formatting :: Color A Cell If It's Divisible By 10
Is it possible to use conditional formatting to to turn a cell red whenever the value in the cell is divisible by 10 ?

View Replies!   View Related
Conditional Formatting: Change The Cell Next To It A Color
I would like to be able to put a value into a cell. By doing this I would like another cell next to it to turn a color. With that said, is it also possible to format a cell with several possible scenarios in one formula to be able to change the cell next to it a color?? Example is this lets say Cell B1 could have the letters, A B C D or E entered. So that the Cell A1 turns say the color Red.

View Replies!   View Related
Color Entire Row If Cell In Columns Contains Specific Word
how to format a row based on text that is in a certain column. For instance, if a cell in column B has text in it I need to format the entire rest of the row with a certain background color and text color. I've been searching around here for a while and I think I'm really close, but I just can't seem to nail it down. Here's the code that I've worked on so far.

Sub Reformat()

Dim SrchRng3
Dim c3 As Range

Set SrchRng3 = ActiveSheet.Range("B1", ActiveSheet.Range("B65536").End(xlUp))

Set c3 = SrchRng3.Find("Europe - *", LookIn:=xlValues)
Do

View Replies!   View Related
Conditional Formatting :: Change Font Color Of Cell
Cell B157 is changed by checkbox 1= TRUE, 0 = FALSE
Cell H1 and Cell H2 are percentages changed by the user.

Range(AI8:AQ8,AI12:AQ12,AI16:AQ16,AI20:AQ20, etc) each cell in the range contains a formula, and is protected from the user.

I need the contents of each cell in the range to change font color (independent of each other) when the formula of any of the cells within the range changes - to the following

If B157 = 1

Cells < H1 (a percentage changed by user), would turn Red
Cells > H1 but < H2 (a percentage changed by user) would turn Yellow
Cells > H2 would be Green

If B157 = 0

Cells < H1 (a percentage changed by user), would turn Green
Cells > H1 but < H2 (a percentage changed by user) would turn Yellow
Cells > H2 would be Red

I need it to fire whenever the value of one of the cells in the range changes.

I have tried If statements, Select Case, and a combination of both but just can't get it!

View Replies!   View Related
Macro Button To Change Background Color Of Specific Cell
My boss wants a spreadsheet that has multiple buttons on it that will change the background color of a specific cell to four different colors. Example:

Text written in Cell B3, Button in Cell A3 that will change the Background color of Cell B3 to either Green, Yellow, Red or Blue.

There will be a lot of buttons on this worksheet following the same format as above. I don't want to change the value of what is in the cell, just the background color.

View Replies!   View Related
Color Table Rows Where Specific Cell In Row Matches Condition
I have a selection on a worksheet that has 4 consistent columns (A, B, C, and D) each with a variable number of rows all containing string type data. With this data I want to use Conditional Formatting so that when I have a string value of X in column D for any row, that whole row with the X in column D has it's color change to say grey, this should happen regardless of what strings are in Columns A, B, or C.

Below is the way I've been trying to do this thus far and failing, when I run this code below I can only get it to grey out the cell with the X not the whole row.

Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""x"""
Selection.FormatConditions(1) . Interior.ColorIndex = 16

View Replies!   View Related
Fill Color: Columns Would Be Blocked With Specific Color
1. By entering a numerical value in Cell A1 for example typing 5.
2. Then, next five columns would be blocked with specific color.
3. if i am typing 10, then 10 columns would be blocked with specific color.


View Replies!   View Related
Detect Cell Conditional Formatting State Of Specific Condition
Is it possible to check the conditional state for a specific condition for a specific cell.
For example a cell has 1 or more conditions.

A condition will toggle to a condition if true or false etc.
The specifics is i use red (color 255) if a cell has not met a specific condition (of 1 or more conditions)
So if the cell is mandatory to be populated, and is blank, it will be red, if not it will be something else.

But i want to loop all visible cells with conditional formatting, and where the cell has a condition when true will show interior color = to 255 return the cell address.
To msg the user where data entry is still required etc.

View Replies!   View Related
Enter Text In One Cell If Specific Text Is Present In Another Cell
is there a formula that will find a word in a sentence written in a cell and if present then enter specif text in another cell? for example, if the word "Hotel" appears in the sentence in cell A5 then put in the text "Hotel and subsistence" in cell B5...

View Replies!   View Related
Row Conditional Formatting: Different Fill Color (same Color For All)
I want every even row that has a value in it to have a different fill color (same color for all). how to do it? I am using 2007.

View Replies!   View Related
Highlight Specific Text Within A Cell Of Other Text
This is the text:

Take 5 PPE Swabs per Area, Both Shifts. Test various equipment - hands, aprons, sleeves, hats, etc.

What I need is for "Take 5 PPE Swabs per Area, Both Shifts." to be bold and highlighted in gray, but none of the other text. Conditional formatting highlights the entire cell, which won't work.

View Replies!   View Related
Text Color Change - Cell By Cell Basis - When Budget Is Reached
I'm a business/application consultant for ERP software, and generally pretty solid when it comes to excel. However, I've come across a question for a personal sheet that I can't seem to solve.

In my personal budget worksheet, I'd like to set a budget on a cell-by-cell basis. When the budget is hit, I'd like the text color (or cell color) to change.

I.E. Cell D14 has a budget of $200. When I enter $200 in the cell, the text turns from black to red. I've searched through google with no luck. I have mildly searched this forum, but haven't found my direct answer - I have come pretty close though, in other languages

View Replies!   View Related
Amount In Cell Is Greater Than A Value, Change The Text Color Of Cell.
is possible to have a cell's text color change if the value of the cell is greater than a certain amount. For example, if the cell is $200 or more the text would change to red.

View Replies!   View Related
When A Date Entered On A Specific Cell, Automatically Enter A Text In Other Cell
I have 6 Headings in excel named...

"A" in cell A1, B in B1, "C" in C1, "D" in D1, "E" in E1 and "F" in F1.

There are two projects.

Project 1 has phase A, D & F and Project 2 has phase A, B, C, D & E.

My Specification follows...

1). Take Prject 1 - Which starts from A...in cell A2 I will keyin "A". When the phase comes to an end I will key in the end date of the phase. As soon as I key in the end date in cell A2 Letter D should automatically appear in the cell D2 and when Phase D comes to an end I will key in the end date in Cell D2 which should automatically keyin F in the cell F2. and is the same case for Project 2.

View Replies!   View Related
Color Cell Dependent On Text Value
Currently F37 has a vlookup code that gives me text values and what I am looking to do is to color code these values.

For Example if the text value is GOOD, should be colored GREEN, VERY GOOD = YELLOW and OUTSTANDING = RED.


View Replies!   View Related
COLOR Items, Text & Cell(s), Unresponsive
I should much appreciate your help on this strange new problem which is twofold:

1. Clicking either color item (for text or for cell) brings no change in designated cell(s)

2. When colors appear on Print Preview they do not on the Printout.

It occurs on Dell Vista Ultimate SP2. Am on IE8; Ctrl Panel list also shows !E7.

It does not occur on HP XP SP3 desktop although no discernable differences in spreadsheet.

On laptop I am unable to use either COLOR icons -- for text or for cells. No trouble heretofore which makes it so frustrating.

Ordinarily, following usual usage, all is well. For unknown reasons, now neither has any effect.

Explored for cause/cure without finding anything more than instructions already well known.

Did see note saying High Contrast might make colors ineffective, so searched out that.

Found how to turn on/off, and easily turned off High Contrast. Made no difference.

View Replies!   View Related
Automatic Text Based On Cell Color
I would like to have a cell automatically change text based on a conditional formatting formula. When the cell color is red, or the formula is true, then display the word "Expired". Can a formula be devised using results from conditional formatting?

This is being used for expired CPR 2-year certifications. When the certification expires, the cell currently turns red (based on a cell formula) and I would also like the word "Expired" to replace the date.

My condition formula is: =TODAY()>A2 + 730

View Replies!   View Related
Color Format A Portion Of Text Within A Cell
I have a column of data that contain miscellaneous comments in each of the cells in column 'A'. This is a sample of the text in one of those cells:

The quick brown fox jumps over the lazy dog.
Is there a way to programmaticly search each cell in col 'A' to look for any occurrence of the string 'fox jumps over' and highlite in RED only that string occurrence?

So in the end the above cell would now be:

The quick brown fox jumps over the lazy dog.

View Replies!   View Related
Conditional Formating: Color The Cell If Value Is Text= Sat Or Sun
I need the formula that will color the cell if value is Text= Sat or Sun

View Replies!   View Related
Format Cell Color Based On Text
On the attached, I want to make the status 3 lines conditional on the text found in another cell.

View Replies!   View Related
Formatting Cell Text...
I am using Excel 2007. I want to put into a cell the symbol for the mean of a set of x values ie x-bar or x with a line over it. One way is to go to Word and use the equation builder, then copy it into Excel. However that restricts me to the default equation font, and I would prefer to be able to use my own choice of font. Is there a way to do this within Excel (perhaps by somehow combining two symbols, the x and a raised bar)? Alternatively can I do it within Word, without using the equation builder, and then copy it to Excel?

View Replies!   View Related
How Can I SUM Values Only When Specific Text In Another Cell In The Row Is Met?
I'm creating 10 'categories' for Column "C" that I plan on using drop-downs to select from. Column "H" has a value tied to "C". What I want to do, is have Excel SUM all of the values in Column "H" where the Category in "C" is a specific text value (e.g. say a category is "hardware", I want a cell with a formula that sums all of the values in "H" where "C" is "hardware".)

View Replies!   View Related
Copying Text To Another Cell After Specific Value
Is it possible for a formula to copy all text after a specific value, to another cell. In this case, after a question mark.

I ask because i've been given 2000+ quiz questions with both question and answer in the 1 cell.


View Replies!   View Related
Copying Specific Text From Cell
Does anyone know of a formula that will automatically copy a specific range of digits in a cell.

Example. I have a cell that contains 1125566348 98. Is there a function in that can copy the first 10 digits?

View Replies!   View Related
Add Text From Specific Cell To Next Empty Cell In A Column
I am trying to create a macro that will allow a user to click a button, enter a text string that will be an email address into a field and have it added to the first empty row in column A or column C on sheet 1. I have not been able to record this with the macro recorder. The input box will be in cell E9 on sheet1.

View Replies!   View Related
Cell Reference Cell Then Append Specific Text Using Vb
This is probably really easy but I don't know what I'm doing wrong.

View Replies!   View Related
Changing Cell Text Color When Out Of List Scope
I have a worksheet with 3 particular columns (L,M,N) that have drop down menus. The menu is populated with items that are referenced on a different workbook (different file). Because there are multiple worksheets accessing the referenced list, this is a master. When I change the master, the lists update, but the cell text does not. Example:

If I chose apple from the list, but later in the master I decide I want apple to be banana, while the drop dowm box will now have banana instead of apple, my cell still says apple.

I don't necessarily want apple to change to banana, but I want a visual cue that this is incorrect. I wanted the cell text to change to red if it is not equal to any of the list items on the master file. I want this to be passive, i.e., I want it to change automatically, so I don't have to press a button or some other activation.

View Replies!   View Related
Display Text And Its Formatting In Other Cell
Without using a copy/paste macro, is there a way to display the text and its formatting from another cell? Obviously, the usual formula "= A2" only brings the text from that cell. Example:

A3: un-am-big-u-ous

G4: = A3 (but I want the font formatting used here as well)

View Replies!   View Related
Formatting Formula And Text In Same Cell
Is it possible to apply formatting to a formula in a cell when you are combining that formula with text? As an example, I want to format the following as a percentage: ="The result is"&" "&(a2/a1)

Currently, it is returning [e.g.] ...result is 0.5, instead of ...result is 50%

View Replies!   View Related
Delete Text In Cell After A Specific Symbol
I need a code for filtering the cell's content.
What i want to filter and delete is all/any text found following after the symbol ">" .

View Replies!   View Related
How To Insert Blank Row Below A Cell Containing Specific Text
I was wondering if someone might be able to assist me with using VB to insert a new row below a cell containing specific text.

For example:


- All of my data is in column A

-I want to scan all of column A, and if there is a cell that contains "ACHCAMERIGROUP M", then I want a blank row inserted below it. If column A does NOT contain that text....do nothing.

View Replies!   View Related
Add Specific Number Of Spaces To Text In Cell
I've got a single column worksheet with a varying numbers of characters in each row.

At the end of each row's cell value I must add a 5 char string. Preceding that string I must have enough spaces to make the total length of each row 106 characters.

I've already:Defined and populated the string. Let's call it "strMyString".Established how many rows are in the sheet and stored it in "lngUsedRange"Written the following which cycles through each row establishing how many spaces need to be added:

Dim lngSpacesNeeded As Long

For i = 1 To lngUsedRange
lngSpacesNeeded = 101 - Len(Range("A" & i))
Next i

Now I just need to know what else to put before "Next i" to locate the end of the existing text in the cell and add the number of spaces in "lngSpacesNeeded" then add "strMyString".

View Replies!   View Related
Copy & Paste Specific Text From Cell
I have a cell that contains various data but in the cell there will be a string of characters that will begin with either ON or TN followed by numbers. What I would like to do is have the sheet look at that cell and if there's a TN12345 (or any combination of numbers) or an ON123456 (same idea as the TN) I would like the sheet to pull just the ON or TN number out and paste it in a specific cell. The ON would have it's own cell to be placed in as would the TN.

Here's an example. C5 contains various data, a mix of text and numbers but it will have either an ON or a TN or both. I would like the sheet to automatically pull the TN number out and place it in A5 and the ON in B5. I would like this process to repeat all the way through the sheet to the max amount of rows since I have no way of knowing how many rows I'll need. Can this be done with a simple IF formula or does it have to be done in VBA or is it not possible at all.

View Replies!   View Related
Search For Files Containing Specific Text In Cell
I cannot find Excel files that contain specific strings. For example, if I create a new file, and in the first cell type in "123456". Then save the file to the folder c:Temp Then in Windows Explorer, right-click on C:Temp and select "Search . . ." Then under "A word or phrase in the file", type in: 123456
It will NOT find the file! It will find a txt file or a doc file, but not xls files.

I thought maybe it fails because that is a number. But I have hundreds of Excel files with Integer numbers stored in "General" format cells and I desperately need to be able to search them to see which files contain certain numbers. It all started with some files that were sent to me - they work PERFECTLY for searching cells for numbers. So strange - yet if I open the file then save it . . . no changes or edits at all, just Open and Save. . . then the search fails for all numbers in that file. This is so perplexing that I have placed 2 zip Sample files on my website for you to Download: http://www.infocellar.com/Samples.zip

Unzip the two files to any folder. One is named "Sample-Works.xls" - Then do a Windows Search for any number in the file, here is one to try that is in the two files: 174026 And it will list "Sample-Works.xls" but it will NOT list "Sample-Fails.xls", even though BOTH FILES ARE IDENTICAL! I copied the same file twice and then named them. The only difference is that I opened the file Sample-Fails.xls and then immediately Saved it, which as I stated earlier, makes it fail all Searches from that point forward.

View Replies!   View Related
Extract Text In Single Cell Based On Font Color
I have a cell that has a formula in it like PREM + PRDCMP + ACQ + DLR in a spreadsheet that looks like this:

PREM PRDCMP ACQ DLR VIP COMM TOTAL
2 1 3 4 6 5

PREM + PRDCMP + ACQ + DLR 2 1 3 4 10

where PREM + PRDCMP + ACQ are colored red and + DLR is colored blue. This formula is entered in that particular cell as a text string. I have a formula in the PREM column that says if the word PREM is located in the formula to the left (and it is), then put the value that is located right under the PREM column (2) in the blank cell next to the formula. So, in the above example, I have an IF statement that sees that PREM is in the formula to the left and so underneath the PREM column, next to the formula, it would place the number 2. Now, I need Excel to detect if any word in the formula text string is colored blue and if it is, to put a zero in that column. So, in the formula above, if + DLR is the only text that is blue, I want to have Excel add 2 + 1 + 3 only. I want to have Excel take any string out that might be colored blue, such as + DLR and return the value of zero for it. The purpose of the different colors in the formula is to distinguish which information is relevant and which information should be ignored. So, I'd like Excel to read the above formula and say put the PREM amount in the cell under PREM, put the PRDCMP amount in the cell underneath PRDCMP, put the ACQ amount in a cell under ACQ and put zero in the DLR column since it is colored blue. If any of them (PREM, PRDCMP, ACQ, DLR, VIP or COMM) are colored blue, I want Excel to put a zero in that column.

View Replies!   View Related
By Typing Specific Text Into Cell A1, It Will Know What Goes In Cell B1
I am creating a spreadsheet that when I type the product series in cell A1, it will automatically know to enter the corresponding catalog # into cell B1. I will be doing this for hundreds of product series.

For Example:

If I type AEP into cell A1, the worksheet will automatically enter #123 into cell B1,

If I type DEB into cell A2, the worksheet will automatically enter #458 into cell B2,

and so on...

I understand that I will have to create a master list with what series goes with each catalog #, but I am not to sure where to go from there?

View Replies!   View Related
Custom Formatting Of Text & Number Cell
I have a cell that contains numbers and text.

example: 1-2-3-4-5-6 or 1-22-3-44-5-6.

If the the number between the dash '-' is a single digit (less than 9), I want to insert a leading zero so all 6 numbers are 2 digits.

Is this possible with custom formatting? I tried a custom for of:
00"-"00"-"00"-"00"-"00"-"00
and it is not working.

I also tried using a MID formula but it gets quite complex identifying the location of the dash - is there an easier way than the MID or LEN?

View Replies!   View Related
Conditional Formatting Based On Text Of Another Cell
I would like to have cells in a certain column turn green if the word in column J of the corresponding row = Not Changed".

View Replies!   View Related
Image Is Hyperlinked To A Cell With Specific Text Entered
Is there any way I can make it so an image is hyperlinked to a cell with specific text entered. After clicking on the image, the cell where the image is hyperlinked to changes text.

For example:

An image of a strawberry is hyperlinked to a cell which contains the word "strawberry".
After clicking on the strawberry image, the cell which contained the word "strawberry" now contains the word "strawberry1".

If you understand that, I would so love the help.

I also posted this is question here

http://www.excelforum.com/showthread.php?t=637811

View Replies!   View Related
Find A Specific Sentence In A Cell Full Of Text
Could you tell me how I can find a specific sentence within a cell that contains many sentences.

for example

I want to find, "I am new." within a text that contains, "Hello I am Bob. I am new. I live in england."

I am currently using =+FIND(AB$1,$V2) where AB1 contains the sentence I am looking for and V2 contains the cell full of sentences. However this returns #VALUE! when the sentence is not found. I want it to return null.

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved