Free Microsoft Excel 2013 Quick Reference

conditional formatting: cell contains text?

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?


Post your answer or comment

comments powered by Disqus
What do you mean by this line:

2. Select Home-> Conditional Formatting (in Styles Group).

I can't find Style Group under Conditional Formatting.

http://www.excelforum.com/tips-and-tutorials/621369-applying-formatting-to-cells-containing-text-only-in-excel-2007-a.html

Regards,

Ganesh P

Is it possible to implement conditional formatting based on text contained
in a cell? Example, user wants to be able to highlight all the cells in a
spreadsheet for a given entity (which will change all the time) so I'm
looking for something like "cell contains xyz" -- but as far as I can tell,
it works only with numeric values.

Am I trying to do something that is impossible?

Thanks in advance.

Which formula to use for conditionally formatting cells containing 6 numbers?

I understand the conditional format process but am breaking down at the validation equation. I have triedL

=$A3="******"

=$A3="??????"

Must be a simple syntax error?

 To protect a cell containing any type of data, two conditions must be met:
Condition 1: The cell is locked:
1. Select a cell in the sheet and press Ctrl+1.
2. In the Format Cells dialog box, select the Protection tab.
3. Select the Locked checkbox and click OK.
Or
Select Home - Format (in Cells Group) - Lock.

Condition 2: The sheet is protected:
1. Select Review - Protect Sheet (in Changes Group).
Or
Right click the sheet tab and select Protect Sheet.
2. Click OK.
Protecting cells containing Text or Formulas, requires isolating the cells containing the type of different data from the rest of the cells in the sheet, locking them, and then protecting the sheet.
 To select and protect different type of data:
Step 1: Change the lock to unlock of all the cells in the sheet:
1. Select all cells in the sheet by pressing Ctrl+A, or press Ctrl+A+A from a cell in the Current Region/List range.
2. Select Home - Format (in Cells Group) - Lock.
Step 2: Selecting cells containing text or formulas:
Select Home - Find & Select (in Editing Group) - Constants or Formulas.
Or
1. Press F5.
2. Click Special, and then select Constants (for text) or Formulas.
3. Click OK.
Step 3: Locking cells containing text or formulas:
Select Home - Format (in Cells Group) - Lock.
Or
1. Press Ctrl+1.
2. Select the Protection tab, and then select the Locked checkbox.
3. Click OK.
Step 4: Protecting the sheet:
1. Select Review - Protect Sheet (in Changes Group).
Or
Right click the sheet tab and select Protect Sheet.
2. Enter a password and click OK.

I have four different sheets in an Excel 2007 workbook.

I am trying to conditionally format cells from the four different sheets to show the highest number in text with red font.

The problem is when I select the cells in one sheet, press Ctrl, and select the cells in the other sheet when I enter the condition the format only activates on the active or sheet in view and does not take into account the cells selected in the other sheets.

Any ideas or suggestions?
Tks

Hi frends,
I have a question.I have put a lot of charts in the worksheet which are
specific to each person. I put the person's name followed by his/her
charts.then I put the next person's name in the adjacent cell followed by his
charts. So the persons names are not in a single column or row. I would like
to format the cells having the text(persons name) so that they appear
prominent. How do I format the cells with a color and bold text.At present I
am formatting each cell having the name and then applying format painter to
the next cell containing text. But it is taking a lot of time. since I have
a lot of entries. On this worksheet apart from the names, I have not entered
any text or number into the cells. Is it possible to write a formula so that
whenever I enter any text in this worksheet cells, it takes the required
format.
thanking you friends in advance

Hi Team,

What conditions do we use to format cells with values (numeric) calculated
using formulae from those that have values not derived from formulae?

Thanks for your help/advice.

Regards

I am trying to build a formula that would say If cell contains text then copy/paste text from a different cell.

For example:

I am writing my formula in cell I5. If there is any text in J5 then I want the text from J3 to appear in I5.

Background:

-What I am doing is making an excel spread sheet for meeting notes.
-Column A has the project names.
-Column I is the date the project was last updated.
-Columns from J on, represent weeks. (Column J is for 1/1, Column K would be for 1/8, Column L would be for 1/15, etc.) The column heading (the cells in row 3) would be the date.

Each week, a new column is added (for a new week) and if there is any new information it is typed for that project for that week. If there is any new text in that week's column, I want the heading for the column (in my case the date for that week) to appear in Column I.

I hope this makes sense; please let me know if I need to clarify anything.

Thank you,

Laura

Is there a way to sum only a conditional formatted cell (or a non conditional formatted cell)?

ex.)

$165,600 (

I have a column of cells that range from 0-1 that have a 3 color gradient scale for the background. 0 is green, 0.5 is yellow, and 1 is red. I also have shapes elsewhere on the sheet that correspond to each cell in the column.

I would like to have the shape fill color match that of its corresponding conditionally formatted cell.

Currently I can get the shape fill color to match that of the cell fill color (not the conditional format color) by using the following code:

	VB:
	
 Range) 
    Dim c As Range 
    Set c = ActiveSheet.Range("SectionC3") 
    If Intersect(Target, Range("SectionC3")) Is Nothing Then Exit Sub 
    Me.Shapes("SecC3").Select 
    With Range("SectionC3") 
        Selection.ShapeRange.Fill.ForeColor.RGB = c.Interior.Color 
        .Select 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If there was something to replace "c.Interior.Color" with that referred to the conditional format, I think it would work.

I am using Excel 2010.

Any help would be greatly appreciated. Thanks!

I have a small excel table. At the bottom of each column I have a cell which is conditionally formatted, returning a '1' or '0' depending on the contents of the column directly above the conditionally formatted cell. I now want to SUM these '1's and '0's in the conditionally formatted cells. Even though there are '1's in some of the cells, the SUM is returning 0.

I am trying to put 2 conditional formats on a cell that contains a rounded number. The format is: if the number in the cell is greater than X +2%, then show green; if it's less than X-2%, show red. The problem seems to be with the rounded number. For instance, my conditionally formatted cell contains the number 7.77 but shows as 8. In this case "X" is 10. The result shouldn't be red but that is the way it is showing. How can I fix this?

Thanks!

I am looking for a VBA code permitting to delete all the spaces in a cell containing text; the spaces can be before and after the text

Thanks

I have a user who reported this, and we have verified the behavior on
different computers. She has a spreadsheet with 10,000 rows and about 50
columns. When she highlights the whole spreadsheet, and selects Format
cells, wrap text ,the cpu levels rise to 100% in Excel 2007 and the process
takes about 5 minutes before it returns to normal. The user perception is
that the system is locked up. With Excel 2002, she was not having the same
experience. The process took about 20 seconds to complete. We duplicated
this behavior on two test computers. Is there anything that can be done to
fix this?

She has a P4 3.0 ghz system with 1gb RAM and an 80 gb hard drive.

Hi guys,
how to get the colorindex of a conditional formatted cells using a
macro?

Hi, I am looking for a formula to link a cell containing text to another
cell containing text / data.

Example: I have a list of 'countries' in Cell A1 (using data validation
- lists). In cell B1 I want to have the 'number of states per country'
automatically populate (corresponding to the country selected in cell
A1). Naturally, I will have to have a separate table somewhere on the
page that links the 2 fields.

My question is 'how do i go about linking the 2 fields?

Any help is much appreciated.
Gin

--
gin2000
------------------------------------------------------------------------
gin2000's Profile: http://www.excelforum.com/member.php...o&userid=34606
View this thread: http://www.excelforum.com/showthread...hreadid=543774


Hi,
I am trying to find a formula that will give me a total of the cells
from certain columns in each row which contain text, for example:

I have text in some cells in columns a to f in row 1. I would like a
total to appear in g1 telling me how many cells contain text from
columns a, c & e excluding b, d & f. (The columns I want the totals
from will be consistant for each row.)

We have tried some formulas and cant get it to work. Don't know if we
are using the wong ones or if we are putting them in incorrectly.

Thanks
Lisa

--
Lisa W
------------------------------------------------------------------------
Lisa W's Profile: http://www.excelforum.com/member.php...o&userid=32497
View this thread: http://www.excelforum.com/showthread...hreadid=522894

How do I send data from a 'General' formatted cell to a 'text' formatted
cell? The end result required is a cell in 'text' format displaying the sent
data.

I wish to use a formula to do this, instead of using right-click, format
cells, select 'Text' as format.

I am new to the VBA for Excel programming. Any help and any advice will be very very helpful.

I have several worksheets in a workbook. in all of them I need to find cells containing text (it is part of the string in a cell) and copy all rows from all the sheets containing these cells into one worksheet one set after another.

Any help and any advice will be very very helpful.

The UDF posted in the forum to count the number of cells of a certain color doesn't work when cells are conditionally formatted. (ex: >3, cell highlight is Green, <2 Red, bet 2-3 Yellow). CountByColor, otherwise works fine for cells with bkground manually colored.

Can someone please help me to get it to work for conditionally formatted cells.

The code I have for CountByColor is as follows:

Function CountByColor(InputRange As Range, ColorRange
as Range) As Long
Dim cl As Range, TempCount As Long, ColorIndex As Integer
    ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
    TempCount = 0
    For Each cl In InputRange.Cells
        If cl.Interior.ColorIndex = ColorIndex Then 
            TempCount = TempCount + 1
        End If
    Next cl
    Set cl = Nothing
    CountByColor = TempCount
End Function
Thanks alot.

Hi, I am looking for a formula to link a cell containing text to another cell containing text / data.

Example: I have a list of 'countries' in Cell A1 (using data validation - lists). In cell B1 I want to have the 'number of states per country' automatically populate (corresponding to the country selected in cell A1). Naturally, I will have to have a separate table somewhere on the page that links the 2 fields.

My question is 'how do i go about linking the 2 fields?

Any help is much appreciated.
Gin

How do I apply conditional formatting to a text box?


Hi,
I am trying to find a formula that will give me a total of the cells from certain columns in each row which contain text, for example:

I have text in some cells in columns a to f in row 1. I would like a total to appear in g1 telling me how many cells contain text from columns a, c & e excluding b, d & f. (The columns I want the totals from will be consistant for each row.)

We have tried some formulas and cant get it to work. Don't know if we are using the wong ones or if we are putting them in incorrectly.

Thanks
Lisa

Dear All,

I am trying to sum conditionally formatted cells with a specific color.

I tried to use a user defined function which adds cell values based on cell color. My problem is that, the following code to check cell color doesn't work with conditional formatting:


Example:
Conditional formatting uses a formula to format cell color based on user input. In the example below, formula highlights the cell where user input and results intersect and I am trying to add these cells (SUM).

User input -> A C A SUM

Result:
A 100 200 500 600
C 200 500 300 500

Thanks, Anil


No luck finding an answer? You could always try Google.