Conditional Formating Based On Specific Text In String In Another Cell
Conditional Formating Based On Specific Text In String In Another Cell - Excel
Hi, Thanks in advance for ur help guys.
I am facing a problem fr\or which i am unable to find a solution. I ve some text in column A and some amount in column C. What i require excel to do is if any cell in column A has "Total" in its text string, the corresponding cell in column C should go BOLD. i.e if A3 has a value of "Maths Total", the contents of cell C3 should become bold automatically. Help me plz. Thanx.
Similar Excel Video Tutorials
Conditional Format Based On Diff Cell
- See how to create conditional formatting based on a different cell. See how to highlight (add formatting to a cell) a number based on a date in a diff ...
National Debt Web Query
- See how to run a web query to calculate the national debt that is incurred each hour. Lern how to use the Excel Functions:
The SUBSTITUITE ...
Conditional Format w DATEDIF function
- See how to use Conditional Formatting and the DATEDIF function to add formatting to a cell based on the content in a different cell. See how to create ...
Im using access 2003 and the conditional formatting is only limited to 3. But i wanted 5. I was told it can be done using VBA. But I've got no programming experience whatsover.
Basically this is what i want, but i dont know how to translate this VB
IF Cell Value = >1 then make the value "Bold", "Bordered", and "Fill" with Orange colour.
IF Cell Value = "H" then make the text "Bold", Bordered, and "Fill" with Green colour.
IF Cell Value = "S" then make the text "Bold", Bordered, and "Fill" with Yellow colour
IF Cell Value = "A" then make the text "Bold", Bordered, and "Fill" with Blue colour
IF Cell Value = "U" then make the text "Bold", Bordered, and "Fill" with Red Colour
Thanks in advance to anyone who can help.
Not sure if this is possible, but I am stuck on getting VBA to recognise a specific ID format/layout which lies amongst other text within the cell. The format goes something like "UR-ACA-###", where ### are always 3 numerical digits. I am trying to write a code to make only the IDs bold, leaving the rest of the text in the cell normal.
The model shall follow the protocol as set out in document UR-ACA-020. Failure to do so will result in the model being rejected. See also UR-ACA-300 for more details.
So basically I want the code to find any text in the format "UR-ACA-###" and make only that text in the cell bold. I tried using the Find & Replace function in Excel but that formats the whole cell.
Hope this makes sense! Thanks in advance!
Hello, this is my first time posting here, although I've learned a lot from reading this forum and have found it very helpful to do searches for information before in the past.
I have a question, though, that I couldn't find by searching through other posts and have been stuck on this problem for days.
I have a row in a worksheet in which the value in cell D4 (and D5, D6, D7, etc) contains a string of text. If part of the text in that cell matches a value I specify, I'd like to format a corresponding cell in that same row to be either a percentage or a whole number, depending on the text.
For example, if a cell in column D contains any part of the text "trade," I'd like to format the cell in column F as a percentage. If the cell in column D contains any part of the text "media," I'd like to format the corresponding row's cell in column F to be a regular whole number with 2 decimal places. The text I'd like to use will not match exactly, though, to the text string in column D. Ie, column D might say "target trade item PPG," but I'd like to use this formatting if any part of the text string in column D matches "trade."
Is this possible? I know conditional formatting doesn't work since obviously you can't format cells as percentages, whole numbers, etc, using this. I've also tried various select case statements, but can't get those to work where the text matches only part of the text in column D.
I'd ideally like this code to work in VBA as a worksheet_change event. Any help getting me on the right path would be greatly appreciated; I've been really stumped. I can provide more details if needed. Thank you,
Hi. I can't figure this out even after searching this forum with keywords such as "font color change macro"
I have text in Column D. It consists of phrases such as:
JUL 2009 EXAM
FEB 2009 EXAM
JUL 2009 LIST
FEB 2009 LIST
JUL 2009 OUTLINE
FEB 2009 OUTLINE
I need a macro to change the font color of the text in Column B of each row based on the text in Column D. If the text in column D contains the word "EXAM", the text in Column B for that row should be Red and bold. If the text in column D contains the word "LIST", the text in Column B for that row should be Blue and bold. If the text in column D contains the word "OUTLINE", the text in Column B for that row should be Green and bold. I know I can do this with conditional formatting, but it must be a macro that actually changes the properties of the text in Column B since I use Excel spreadsheets to do batch search and replaces in Word documents with a program called Useful File Utilities and it does not recognize the conditional formatting. I only want to change the font color for text in Column B. The other columns should stay the same. Thanks.
I need to compile a "MTD" tab in my excel spreadsheet to automatically carry over an amount based on a specific string name with an amount that changes weekly.
Tab name: 11.03-11.09
Cell B2: Exchanged Names
Cell C2: 10
Tab name: 11.10-11.16
Cell B3: Exchanged Names
Cell C3: 7
Can we pull the above data and place it in a "MTD" tab automatically, even though the cells and amounts change?
The name remains constant "Exchanged Names" But the amount of instances: 10 & 7 changes, as well as the cells.
I need it to be able to search through, find "Exchanged Names" and add the 10+7 to = 17 for the MTD total.
On the plus side, the string "Exchanged Names" will always stay in column "B" and the amounts will remain in column "C" if that makes it easier.
Thanks & LMK if an example is needed.
Hi. I'm going to try to be very descriptive so that all the bases are covered with just one post from me.
Excel 2003 on Win7
I have a spreadsheet that contains the following info from my music collection:
Column A: Directory name (Elvis_Presley-Greatest_Hits_Vol._1-2CD-2010-EOSiNT, for example)
Column B: File count (a count of the mp3 files in the directory)
Column C: File size (total size of all mp3 in the directory)
Column D: Average bitrate (192k, for example)
Column E: Bitrate type (CBR or VBR)
Column F: Bitrate quality (mp3 encoding info - not important)
I would like to use conditional formatting (I'm assuming with a formula or function) to find all instances of the string "-XXX" and have it format the whole ROW with a red background/pattern. If it's possible, I'd like to search for more than just one text string in a single formula/function ("-XXX", "-YYY", "-ZZZ", etc.). The dash will ALWAYS be part of the string, but there may be a need for a wild card at the end of the -XXX string because there may be more text after the 3 letters (like -XXXaAA) in some cases.
I've figured out how to do it if a CELL has a specific value (like CBR or VBR), but using just part of a cell's value has me stumped. I have 2 conditional format slots left (the first is already being used) in case that will make a difference.
If this requires vb, I'll need step by step instructions on how to use and implement - never used it before.
Can anyone help a guy out here? Thanks very much in advance!
I'm very new to writing macros and I am trying to format parts of a cell text to bold.
The normal cell in the column that I want formatted uses the formula:
";"Requirement: ";I4;" ";J4;" ";M4;" ";N4)
and this returns something like:
Requirement: The system Shall provide standard window handling
What I want to format is the text Pre-Condition: and Requirement: to be bold Is this possible to do with a macro? or is there another way to do this? Your help is appriciated
Using Excel 2003, I have two columns;
Column "A" contains various user-entered text values, dates, or
numbers. The cells in Column "B" are used to display the date onwhich
the corresponding cell in Column "A" was last modified. This is
automatically updated with today's date every time the value of the
corresponding cell in Column "A" is updated.
For example supposing I changed the value of a cell in Column "A", from
"Delivery expected OK" to "Delivery expected Late", the current date
would be entered into the corresponding day in Column "B".
My first problem is highlighting the fact that changes to the data have
been made, as the spreadsheet will likely grow in size over time.
Therefore, I am trying to implement VBA to highlight the cells in both
"A1" and "B1" based upon the value of the date in cell "B";
If the "Date Modified" (Cell B1) is within 2-days of the current date,
it is to be formatted as Red&Bold, otherwise, standard black
formatting. This will show all the recently modified date values in
red, and thus, after the cell has been unchanged for 2 days, it will
resort back to standard formatting.
I have put a lot of effort and time into this, but with little/no
My second problem is the formatting of the cells in column "A", based
upon the formatting of the corresponding cell in column "B", as
previously discussed. If the formatting of "B1" is red&bold, so must
the corresponding cell, A1.
The column "A" already has 3 sets of conditional formatting in place -
so maybe this has to be done in VBA?
Any help would be much appreciated.
Thank you for your time.
My excel skills are pretty limited. I can do siply conditoinal formating, but what I am trying to do on one sheet is the following.
I've got a workbook with 2 columns that I want to check and compare.
These are text/string colums not numbers.
Here is some sudo code:
For Column A,
if the text is "ABC" or text is "DEF" or text is "GHI" or text is "JKL" then color red and bold.
For Column B,
If Column B is "XYZ" and Column A is "ABC","DEF","GHI","JKL" then color yellow, and put text "foo" in colum c.
As the text list may change/be appened quarterly, It would be nice if the text was stored in some colum, so it be be more like If Column a matches any strings in colum d, then colum a is red and bold ...
I hope I explained this okay.
What I want to do is I have one cell which we will say is "A1". This cell has multiple lines worth of text in it, some of which is bold and some of which is not. What I want to do is make another cell, lets say "B2" identical to "A1".
If I use the following code:
Range("B1).Value = ActiveCell.Value
the text is transfered across but now all the text is not in bold. I want to keep the same text formatting that is in cell "A1"
Any help is much appreciated
This is a following up of a former thread of mine, see http://www.excelforum.com/excel-prog...bold-text.html.
I want to have a function or macro that if cells have mingled bold text, that only the bold text shall remain in the cells, and all the rest of the text must be deleted.
Is this possible?
Perhaps a hint is (I don't know) to start from the useful formula of PMO which he gave me in the above mentioned former thread :
Function ISBOLD(ByVal cell As Range) As Variant
If cell.Font.Bold Then A$ = "TRUE"
If Not cell.Font.Bold Then A$ = "FALSE"
If A$ <> "TRUE" And A$ <> "FALSE" Then
For i& = 1 To Len(cell)
If cell.Characters(i&, 1).Font.Bold Then
ISBOLD = "MINGLED"
If A$ = "TRUE" Then ISBOLD = True
If A$ = "FALSE" Then ISBOLD = False
Hi there, good day!
I am not sure if conditional formatting can do this but I want to search for a text in my
entire sheet (Log File) of 500 entries and state that;
IF any cell in Column B has the string "Unknown", then highlight corresponding Row (yellow).
Also, IF any cell in Column B has the string "ERROR", then bold (in red) the words in the cell.
Any help will be appreciated. I've attached sample file in the 'Log File (My Requirements)' tab.
Here is a challenge I am facing.
1. Two Columns w/ Integers - Column "A" and Column "B".
2. Conditional formatting testing if Values in Column "A" are less than Values in Column "B"
3. If the condition is true then a cell in the column "A" (Font = Bold, Text Color = WHITE, Cell Color = RED). Otherwise initial formatting is kept.
I did this via standard Excel functionality - Conditional formatting, and not via macros, because the Values in the Columns can change dynamically (updated by user), therefore I want the conditional formatting applied right away after the change is made.
So here is the problem:
In the beginning of every month I need to delete Column "B", Column "C" becomes Column "B". However, the conditional formatting "looses" the refernce area (which was column "B"), and gives "#REF!".
I would like to create a macro that would modify the rule after deleting Column "B", and change the "#REF!" to the "new" Column "B" (formerly Column "C").
I do it w/ the following code:
Columns("A:A").FormatConditions(1).Modify xlCellValue, xlLess, "=$B1"
However, I would like the user to tell Excel via the InputBox Function which column to use as reference for comparing to column "A". And then modify the conditional formatting accordingly (to reference to the user defined column).
When I put the following code:
Dim Msg As String
Dim R As Range
Msg = "Select a cell in the reference column"
Set R = Application.InputBox (Msg, Type:=8 )
Columns("A:A").FormatConditions(1).Modify xlCellValue, xlLess, R
The conditional formatting is done based on Values in the "R" Range, but not the the reference to the cells in "R" Range... Hence the conditional formatting is no longer dynamic because if Values in "R" Range are changed then you need to re-run the macro. But if I could make Excel take "R" as Range and not Values from "R" for the conditional formatting, it would have been perfect.
If have a similiar task like in this thread (Help extracting a particular string of text from a cell).
But it is a bit of a harder nut (for me). I try to explain; the content of the Data-Cell could look like this examples (between "" the content, the desired output bold):
"abcxyz" -> "" (Nothing)
"CR 256, 278, 296"
"CR 256; XYZ 412, 478"
"CR 256, 278, 296; XYZ 412, 478"
"ABC 1232, 1468; CR 1256, 1278, 1296"
"ABC 1232, 1468; CR 1256, 1278, 1296; XYZ 412, 478"
"ABC 1232, 1468; CR 1256; XYZ 412, 478"
Trying to sythesize I like to extract the first number after the String "CR " and before the comma (variant A) or semicolon (variant B).
I was able to find a solution to extract the string before the semicolon (or end of string) if there is nothing before the string "CR " with this code (working for example 1-2 & 5, working in part for example 3, 4 & 6, not working for example 7-9):
=TRIM(MID(Cell,FIND("CR ",Cell)+3,FIND(";",Cell&";")-FIND("CR ",Cell)-3))
I hope the masters of Excel code give me a help in this.
See post # 7 for better outlined examples and limitations.
We know that conditional formatting takes the value of a cell, and thereby determines its format.
I'm looking to go the other way, or at least partly. I'm wanting to use the format of a cell in a formula.
As an example, I have a column of data: some of which are bold+italicised, and others of which are normal text, however, the text formatting implies something specific about those particular entries.
I would like to be able to return a value of TRUE or FALSE, depending on whether the cell text is bold (or bold+italics, or italics), or just plain text.
In looking for a solution to this problem I've done some forum searching here and elsewhere, and looked at the built-in help - and I've come across the various arguments of the cell("info type",reference) formula.
In this, I am able to isolate a few properties of the cell (including text alignment, number format and others) but not whether the text is bold/italicised or similar. As a workaround, I've manually right-aligned the text that is bold+italicised, and I can then grab those in a formula: if(cell("prefix",A1)=",something,something_else) - but is there a way to do this without the 'alignment' workaround?
As an additional question, is there a formula argument/formula-based solution that returns cell fill colour? I'd like to try and address this without having to play in VBA, if possible.
Thanks for your assistance
I have tried searching for a way for VBA to recognise in VBA if a cell contains any text, but most of my searches respond to specific cases for specific characters in a string
Basically, I'm just looking for the correct way to word a string of code that if it detects any text in column B (col 2 ) then it should not do anything, else it should cut the value in col 2 and paste it in col 8
'''''''''''''''''''''''''''''Check for values in column B''''''''''''''''''''''''''
' For i = Range("A65536").End(xlUp).Row To 5 Step -1
If Cells(i, 2).Value "<> TEXT? this is where I need help" Then Cells(i, 2).Cut Destination:=Cells(i, 8)
Similarly, this can be rethought as determining if the cell is an integer, ie if it is an integer (and not text) then cut from column 2 and paste to column 8
I have a VB / Excel97 formatting problem. I want to include the text from
a specific cell on a specific worksheet in the header on a number of
worksheets, and I want the text to be formatted with a specific font. I
can get the header to display the text in the cell or I can get the header
to format text with a specific font, but I cannot get the header to display
the cell's text in the specific font.
ActiveSheet.PageSetup.LeftHeader = "&""Times New Roman,Bold""TEST"
displays the word TEST in TNR bold font
ActiveSheet.PageSetup.LeftHeader = Range("B10").Value
displays the contects of cell B10 in the header in Arial
(the default font)
I've tried various combinations of these two commands, but cannot get what
I need. How do I get the header to display the cell text in TNR bold font
So I am tasked with a specific spreadsheet at work I am tearing out my hair over.
What is requested of me is to make it so if one field is reporting a certain value (in this instances "No"), then the formula will change the next seven fields to say "N/A" automatically.
I am currently unable to figure out how to create a formula or conditional formating in just one cell that will change multiple other cells.
Can anyone help?
I have never tried a formula like this and don't even know what function to begin with out side =IF(B62="No"
then i don't know where to go from there.
Newbie to VBA
I need to use Conditional Formatting to format a range of cells to have
Wheat background and Red text (Bold) if a text string is contained in
each cell. For example, all cells in a range that contain the text
string "LLC" or "Inc" or "Corp" or "Corporation" or
"Company" , etc.
If cell E2 contains Acme, LLC
I need to give that cell (E2) the condition formatting
If cell E3 contains John Brown
That cell does not get the condition formatting
If cell E4 contains Joe's Pool Hall, Inc
Cell E4 gets the condition formatting
Thanks to Dave Peterson I know how to dynamically find the last row or
all rows used in a spreadsheet as follows:
'Count the number of rows used in a worksheet
oRowMax = wks.UsedRange.Rows.Count
oRowMax1 = wks.UsedRange.Rows.Count + 1
I know how to manually use conditional formatting within a spreadsheet
but when using formulas in CF all I can find is when a cell is "equal
to" a value. Can someone tell me how to use "contains" in VBA CF
code or if it can be done? I would also like to incorporate the code
(MaxRow) from above if possible.
Thanks in advance for any help.
I am trying to write some VB code which can modify a cell's data. Column A contains the main string. What I want to do is look at each cell in Column A and if the slash character "/" exists then do one of two things:
1. If the string is "/L" then copy the "L and any text after that to the same row in column G
2. If the character after "/" is anything else then don't copy anything to Column G
Next, in the column A data, only keep the text preceeding the "/".
If there is no "/" in the cell, then do nothing.
Basically I have a column of serial numbers but only some of the cells have extra information which is displayed after the "/" character.
eg. If cell A1 has the string "aaa123/L001" , I want cell A1 to now read "aaa123" and cell G1 to read "L001"
eg2. If cell A1 has the string "aaa123/Server", I want cell A1 to now read "aaa123" and cell G1 empty.
Thanks for your help.
I am trying to extract text from a text string that corresponds to text in another column. For example, in the spreadsheet attached, I am specifying which database to reference in cell E6. Next, I am using cell D6 to specify which "Split" to use from the database. I need to be able to change these 2 parameters and have cell Q6 display the correct "Phase". The text string in the first column is some combination of "Split (cell D6) Phase (#1-16) Coor Phs. I want to display the "Phase" that corresponds with the text "ON" in the second column. I hope this makes sense.
Thanks in advance for your help.forum version.xlsx
Thanks in advance for your help.
In column 1, I have a text string which includes a numeral somewhere
in it. In column 2, I have numbers.
In column 3, I want to search the text in string in column 1 to see if
there is a "6" anywhere in the text and, if there is, I want to place
the number in column 2 into the current cell.
I thought a simple =if(A1 = "*6*", C1 = B1, 0) would work but it
always returns FALSE and, therefore, zero.
Hello helpful community, I am stuck with the following issue: I have a report that has a list of different numerical values (open balances of invoices) in column T that I am adding subtotals to based on how many days past due the invoices are. The subtotals sum the invoices that are 45+ days past due, 30-45 days past due, and < 30 days past due. Is there a way to automatically place text in the cell adjacent to the subtotal to label it is either "Aged 45+ Days", "Aged 30-45 Days" and "Aged <30 Days"? The subtotals could occur in any cell of column T based on the number and age of the invoices when I pull the report. I am also trying to format these labels in bold text and right justified...
Is there a way to do this? Let me know if I can post more helpful information on my problem.
Thank you, any help is greatly appreciated!
I'm looking for a macro which will search a column and detect bold text and mark it with HTML charcters for example "in this text some words are in bold font others are not" I would like that replaced with "in this text some words are in <B>bold font</B> others are not"
I can see in a macro how to select text as bold but not how to detect the start and finishing of bold text
Any help would be much appreciated,
Thanks in advance
In my Excel Sheet ,I want to make the Font Bold for some specific condition.The condition is such that if the value of the cell is not to equal to "0.00",then it should make the value of the cell Bold.
The cell contains Vlookup formula which is as follows:
If I try using Conditional formatting,such as Cell value not equal to "0.00" ,the cell with the value "0.00" also changes to Bold..
which should not be the case....
For ex- Cell "A1" contains value 9.80----->should be Bold
Cell "B1" contains value 0.00------>should not be Bold..
Plz help me with some solution