I have a workbook with numerous worksheets that contain text, steps actually. I would like to select all common words and change their formatting to bold. Every time I try however, it formats the entire cell in bold. Is there a way to isolate just the word "click" for instance in all the cells in each worksheet and change the formatting to bold, and leave the other text within the cell normal?
View 9 RepliesIs it possible to perform conditional formatting on numerous worksheets at one time. I have about 50 worksheets that I would like to perform the same conditional format on. Is there a way to do this.
View 5 Replies View RelatedI am trying to write this code where I need to save an excel workbook with a number of tabs to 1 text file with a name of users choice.
I am able to do this if there is a separate file for every worksheet within the workbook but not if we have the same text file to append every time.
I have one sheet for each day of the month with a table that has Employee Name, Reason, and Expected Return. I need to copy each line into the monthly sheet, but each day may have varying number of rows. Needless to say I would like to do this without copying lots of blank lines into my consolidated sheet.
View 10 Replies View RelatedMy workbook is a variation of the below repeated on a number of sheets.
Sheet 1
A
B
C
1
Name
Carrots
Peas
2
John Smith
0
7
3
John Doe
2
1
4
Joe Bloggs
4
0
On a separate summary sheet Im struggling to create a formula that would look through all sheets and Sum where 'John Smith' appears and then sum the number of 'Carrots' (if any) and 'Peas' and then the same for any other names that has values attached to it. The names in each sheet may not be in the same order or appear at all otherwise I would just use a standard sum on each sheet.
I believe in the past that I have told Excel to send the output of the pivot table to different worksheets. For example if I have three regions- the pivot table would send the output to three worksheets and name the worksheets with the three regions.
View 5 Replies View RelatedIs it possible to apply changes to formatting (column width, text size) to all worksheets within a workbook? I have a workbook with 50 worksheets--1 for each state--and I want the formatting to be standard throughout, and I want to do it quickly.
View 3 Replies View RelatedTrying to create a button that, once clicked performs the following task:
Checks whether the text in a cell in the column X2:X40 matches the text in a cell in the Range A2:U14.
If it does, the formatting of the cell in X2:X40 (eg background) must change to match that of the corresponding cell in A2:U14.
Only 1 cell in the range will match 1 cell in the column
For example:
Before click:
Cell A9 has "John" written in it and a yellow background
Cell X4 has "John" written in it and a clear background
During click:
matches these cells
changes X4 so that it also has a yellow background
After click:
Cell A9 has "John" written in it and a yellow background
Cell X4 has "John" written in it and a yellow background
1. Search an excel sheet "column" for a particular type of text and insert values based on that text (if found) in another column.
e.g I have column A1 to A10 with different types of text. I would like to search for the keyword "Risk is high" OR "Risk=High" for each cell in the column and insert a "1" if found beside it's corresponding "B" column. If not found, I would like to insert a "0".
So, if the text "Risk is high" OR "Risk=High" was only found in A6, I would like B6 to be "1". Rest of the values in the B column would be "0's", since the text was not found in any of the other cells.
I have a macro which imports data from a mainframe dump text file and performs 'Text to Columns' on the imported data so that formula in the spreadsheet can act on the data. The code works perfectly well when I use it, but if a different user logs on and performs exactly the same mainframe dump and import macro the Text to Columns action splits the raw data in a different way and the result is that the split renders the formulae useless.
I've experimented a little and for some reason it appears that the 'Field Info' parameters which are produced when the Text to Columns function is recorded in a macro differ between users even though the raw data is exactly the same.
FieldInfo:= _
Array(Array(0, 1), Array(18, 1), Array(35, 1), Array(56, 1), Array(70, 1), Array(88, 1), _
Array(102, 1))
I have this code (got it from here: [url] that saves worksheets as separate workbooks. It works perfectly fine for that but I would like to save each worksheet as a text file (tab delimited). I tried changing FileFormat:=xlNormal to FileFormat:=xlFile but a debug error occurs.
Sub MakeMultipleXLSfromWB()
'Split worksheets in current workbook into
' many separate workbooks D.McRitchie, 2004-06-12
'Close each module AND the VBE before running to save time
' provides a means of seeing how big sheets really are
'Hyperlinks and formulas pointing to other worksheets within
' the original workbook will usually be unuseable in the new workbooks.
Dim CurWkbook As Workbook
Dim wkSheet As Worksheet
Dim newWkbook As Workbook
Dim wkSheetName As String
Dim shtcnt(3) As Long
I have a workbook with about 20 different worksheets in it. Each one has a header with three text boxes in it. I would like to fill these text boxes for all of the sheets, but I would like them not to be linked together or to any one cell, so that the individual fields can be modified after they have been set.
My code is below. When I run it, I get an error - "Compile Error: Method or data member not found."
hwrev = InputBox("What revision is your hardware?", "HW Revision")
eswrev = InputBox("What revision is your embedded software?", "ESW Revision")
sheetcount = 0
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
I have a macro that is supposed to type a text in column 8 if condition is met, on all sheets but one (" Total"). When I run it, it stops at "Next Line" with a "Compile error: Next without For". For the life of me, I can't figure out where I'm missing a "For".
Sub ForceMatch()
Dim ws As Worksheet
Dim line As Integer
endline = Range("A1000").End(xlUp).Row
For Each ws In Worksheets
If ws.Name <> "Total" Then
With ws
For line = 3 To endline
If (Cells(line, 9).Value <= 5000 And Cells(line, 8).Value = "") Then
With Cells(line, 8).Value = "FORCE-MATCH CANDIDATE < $5K ICDP"
Next line
End With
End If
Next ws
End Sub
I have a large list composed of three columns, First name, Last name, and Email address. For the most part names and emails match up perfectly but there are an unknown number of names that do not match their email address. Here’s the hard part, email addresses are in different formats for example:
some are firstname.lastname@somewhere.com but others can be lastname.firstname@somewhere.com or some email addresses are first name initial then full last name with no dot separator @somewhere.com and even turned around like Last name initial then first name. For the most part this is the only four ways the emails are configured but all email addresses have some variation of last and first name in them.
How can I find the number of email addresses that do not match their names?
I warned you that it was hard.
I'm looking to see if I could use any sort of formula to have 1 number appear Red and another number appear Black all in one cell
In 1 Cell: 80 (75-95)
I'm looking to use this formula, where I'd like my A1 to be Red
=A1 & " " & A2
I have text in a cell like this:
MINOR Ø1.2314-1.2414 ([-XX-] "Minor" Ø1.23640 +/-.00500, SF1015-16)
The cell width is 35.71", this causes the text above to wrap. The part I am having trouble with is getting the text to be readable without portions of the text being split, such as:
MINOR Ø1.2314-1.2414 ([-XX-] "Minor" Ø1.23640 +/-.
.00500, SF1015-16)
Ultimately for readability I would like the text to read like this:
MINOR Ø1.2314-1.2414
([-XX-] "Minor" Ø1.23640 +/-.00500, SF1015-16)
Within the same cell.
The second string will always have a " (" at its beginning, a "space" and a "left bracket". Of course the string of text will always vary in length and some times only one character wraps or a majority of characters wrap.
I have to format huge text file to columns. Here is the file: [URL] The whole file should look like the first 15,16 rows. What is the fastest way that can be done? Here is the original file: [URL]
View 3 Replies View RelatedI 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 2 Replies View RelatedBasically, I have a Rule that says:
=$A1="Mitigated"
I want the same format if Column A says either "Mitigated" or "Accepted".
I can't create a new rule as I have to save the file in 2003 for other users and I've got another 2 rules for varoius other things.
I've tried crudely putting "Mitigated"OR"Accepted" and it dosen't work.
But the import tool I am using for one of our applications is requiring us to save using Excel 97-2003 Workbook. I have converted a couple coulumns to text, but inorder for the upload to take, I have to add an spostrophe infront of the number to make it "text" and upload cleanly. I want to do a find and replace to edit the columns with all the differing numbers. How do I do that? I tried to replace ?????? with '?????? and it doesn't work...
View 3 Replies View RelatedI have an excel spreadsheet that contains dates and times however it appears that some of the dates are in text format Is there a way i can format the data fist so they are all dates?
View 5 Replies View RelatedI have two columns, one is with number and one is with text, what i have to do concate both columns data in one column with formatting. but when i m adding with & operator or by concatenate, formatting disappear.
I am automating an invoice template for an excel novice, thus I want to conditionally format certain cells dependant on what text is in them.
I want to do this in VB but I don't know how!
Why does a cell formatted as "text", display ### but shows the actual words when formatted as "general?"
View 13 Replies View RelatedI would like to conditional format a range of sells which contains numbers formatted as text. I would like if the sell contains the specific number (which is formatted as text) to be formatted in red. It works for one value but I have to list 15 values for which this condition to be done.
For example: if some of the cells in the worksheet contains one of the following values: 265987,265489,235698,2356987,2356987,2356987,235569,...ets - 15 conditions - to turns red.
I'm looking to build a macro which will automatically transfer a line of text from one worksheet to another within the same workbook. Once the macro has been run, the text in the original sheet should disappear and re-appear in the other worksheet. It should appear in the same column, but different line from the original.
I have prapared a formula for B2,B3 and C2,C3 and the reult came in B10,B11,C10,C11 and D10,D11,E10,E11.
From that relust i want to get the final result should be as follows.
B10 should again divided in to anil(in one cell) and 16/12/2013 11:33:50 (in another cell). This formula to be added in B10,B11,C10,C11 and D10,D11,E10,E11 formula.
I loooking for the final reslt, i have explain the in the excel. I reuired the final result with single formula from B2,B3 and C2,C3.
I have a cell containing a post code (U.K). For example CF678JU however I need it to be formatted to show CF67 8JU. Is there a quick option of setting a formatting rule to add the space instead of me going into each cell and changing... (there's thousands of them!!!)
View 2 Replies View RelatedI have a program in unix that ouputs a text file in .csv form which allows me to import my data into an excel spreadsheet very easily. I am wondering if there is anyway I can add formatting, like to tell excel to make a cell green or something like that.
If it can't be done with .csv, is there any other tool or format available that will accomplish this?
When I export my files onto text and open it with Excel, it will automatically format the cells to take out leading zeros, convert to scientific notation, and convert certain numbers into date/month/year format. How can I get Excel to open my file without reformatting the numbers in my text file?
View 4 Replies View Related