Changing Colours Of Certain Words In String?

Feb 22, 2013

I have a conditional formula which populates a string depending on the value selected by user.

E.G of formula is:- =IF($B$1=0,"CONTACT PERSON - OPTIONAL",IF($B$1=5,"CONTACT PERSON - MANDATORY",""))

Where the result is "CONTACT PERSON - MANDATORY" then I want the word MANDATORY (not the whole string) to be displayed in RED.

View 3 Replies


ADVERTISEMENT

Changing Colours Of Part Of String

Feb 22, 2013

I have a conditional formula which gives a value based on what the User specifies.

Example of formula is:

=IF($B$1=0,"CONTACT PERSON - OPTIONAL",IF($B$1=5,"CONTACT PERSON (FINANCE ONLY) - MANDATORY",""))

If the result of the string is " CONTACT PERSON (FINANCE ONLY) - MANDATORY " then
- word MANDATORY (not the whole string) should be in RED
- words (FINANCE ONLY) should be in BLUE
- remaining string should be left in BLANK

Not intending to use VBA. Is this possible?

View 10 Replies View Related

Changing The Colours Of A Userform

Oct 8, 2008

I would like to change the colour of a userform both the background colour and the font colour. However, in the colour property box I am met with &H00C0C0C0& (which is Grey). I can select some other colours but not the one I would like to use.

I have tried inserting RGB(194,214,154) however I get the error 'Invaild proprty value'

View 3 Replies View Related

Changing Colours On Conditions

Mar 5, 2008

i am creating a spreadsheet to account for work being done on a team. At the moment the sheet adds up the average time that the whole team is taking to do a task,and it also adds up the time each person is taking.

so for instance: KL take 100 minutes to do the task. but the average time across the team is 90 minutes per task.
i have my list of staff with their times in column E. so what i want is if their time is higher than the average then the box turns red, if it is lower then green etc.
(the average figure is got by summing everyones totals in box B22.

View 9 Replies View Related

Formula For Changing Date Different Colours

Nov 10, 2008

i need a formula that does the following:

if the date is two months under the date it needs to be checked it goes yellow
if its over two months before it needs checked it goes green
if its passed the date it needs to be checked by it turns red

View 5 Replies View Related

Changing Chart Series Colours In VBA

Jan 21, 2010

I have a spreadsheet which reports across different categories, but there's a lot of info. So simplify this, I've written a simple macro that changes the series range in an existing chart, so top level and by category. This works well apart from I'm using excel 2007, and some people aren't, so the default colours look fine on my version, but change to some awful colours on anyone with an earlier version of excel. I need a way of changing the colours to standard ones depending on which macro is being run.

I have tried running the following, but I get an error, basically saying the series hasn't been selected.

ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).Interior.Color = RGB(255, 0, 0)

View 9 Replies View Related

Changing Line Colours In Multi Listbox

Jun 23, 2014

Is it possible to have a listbox with each line with a different colour?

View 2 Replies View Related

Top 3 With Dark Colours - Remaining 2 With Light Colours In Conditional Formatting?

May 6, 2014

I'm sure this will be an easy query.

These are marks scored by 5 people in 10 exams

Name of the student
MARKS
MARKS
MARKS
MARKS
MARKS
MARKS
MARKS
MARKS
MARKS
MARKS[code]....

Range
A1 to K6

In each row top 5 MARKS should be filled with BLUE,VIOLET,YELLOW,RED AND PINK

If its not possible. Top 3 with dark colour, remaining 2 with light colour using conditional formatting

View 1 Replies View Related

Changing Cell Colours Depending On Cell Content

Nov 19, 2008

What I need to do is have a cell that will be say yellow until there is information put into this cell. The information could be in the format of text or numbers. The information would not always be the same so it would need to be yellow when there is no information in the cell and another colour or white when there is information in the cell.

View 6 Replies View Related

Metallic Background Colours / Other Colours

Oct 29, 2007

The idea came about after wanting to show a 'gold' 'silver' and 'bronze' background colour, but having to plump for yellow, grey and orange looked a little crap :f

If not, can a workaround be created by using code and a picture or 2?

View 9 Replies View Related

Changing Numbers From Numerics To Words

Jun 20, 2006

Is there any formula or easy way to automate the changing of a numeric input into words, eg if you input 100 in one cell, the output in another would be "one hundred"?

View 9 Replies View Related

Remove Certain Last Words From String

Feb 15, 2008

I'm attempting to extract the Street Names from an Address.

For Example, if given "123 Very Big Street" I'd want to extract "Very Big".

If given, "123 Very Big" I'd want the same answer.

I've written code to do this, with a simple IF statement to identify mathcing last words like "Street" and "Drive".

How can I efficiently do this with a large list of last words? I'm drawing a blank on how to query against an array that would contain all these values.

View 12 Replies View Related

Match Words To A String Using VLookup

May 10, 2014

Take a look at the attached workbook.

Tab 'Internal' is an example of our internal customers and how much they spent with us.
Tab 'External' is a list of companies in our area that we could do business with, it came from a marketing firm. Columns B:J contain my formulas. B is my lookup.

I need to match the two sheets together and pull the dollars spent from Internal to External. The problem is that the marketing firm did not use the same naming conventions that are in our systems. This means a simple Vlookup will not work.

Here is what I did so far: used VBA and formulas to break apart the company names in External, then using a modified Vlookup to match the words to the names in Internal. My accuracy rate is less than 1%.

View 1 Replies View Related

Search String Of Words In Text?

May 27, 2013

I am trying to search words that are in column A (5 letter combinations of sequences) within the text in column B (amino acid sequence).

So I am stumbling upon 2 questions:

- what is the function that would do this search.

- how to acchieve to reverse the text in B1 cell (eg. abcde --> edcba , but with 600 letters),

sample file: test.xlsx

View 4 Replies View Related

Switching Words Around In A String Of Characters?

Oct 10, 2013

I have a sheet of about 10k rows that I need to switch some things around on. The string is below:

Acura|TL|1995^^Acura|TL|1996^^Acura|TL|1997^^Acura|TL|1998^^Acura|TL|1999

It needs to say:

1995|Acura|TL^^1996|Acura|TL^^1997|Acura|TL^^1998|Acura|TL^^1999|Acura|TL

How to fix this with a macro or formula. The Make Model and Year on each string will be different. Basically I need the format to be Make|Model|Year.

View 9 Replies View Related

Extracting Words From Word String

Oct 13, 2013

Let's say i do have words : cow, dog, giraffe, elephant, parrot.

The rest will try to explain in the excel file.

View 9 Replies View Related

Find First Two Words In A String And Compare To Another With VBA?

Mar 28, 2014

What I am trying to do isn't very complicated. I started on some code, but it's just not good enough to do all of what needs to happen.

A
B
C

1
Cupcake Monster
Aisle 5
Shelf H

2
The Cupcake Ghoul
Aisle 2
Shelf P

3
Fred Baxter's Diary
Aisle 1
Shelf X

4
Angry Cupcake Beast
Aisle 3
Shelf A

5
Everyone Loves Cake
Aisle 4
Shelf R

So I have a list of titles that I want to search for a particular string. The title in this list is the "proper" title,whereas the rest of my workbook typically uses a shortened version of the title. Sometimes the two will match 100%, but usually, they will be close.

If the string in the table above has more than 2 words, I want to use the first 2 words to check for a match. If the string has 2 words (will never have fewer), I want to match the first word. I want to do this IF there is no 100% match (if possible or reasonable). I will settle for just matching 1 or 2 words. I want to copy the values to another sheet and then delete the row that I originally got the data from.

What I want to do is search the list for "Angry Cupcake" and return the Aisle and Shelf location of Angry Cupcake Beast. There will never be any other "Angry Cupcake", so I am not worried about mismatches there.

Code:
Dim SearchRow As Integer
Dim StoryTitle As String[code]....

how to do and can get to work without issues is an exact match. This still leaves out all of the shortened titles though.

View 3 Replies View Related

Split Apart A String Into Individual Words

Apr 30, 2014

I can not get this to work:

Code:
Sub SplitApart()
Dim data As String
data = Sheets(1).Cells(20, 1).Text
For Each EachSplit in Split(data)
n = n + 1
Sheets(1).Cells(20, n + 1) = EachSplit
Next
End Sub

Error code when Debugging on "Split":

"Wrong Number of arguments or invalid property assignment"

View 4 Replies View Related

Find Words In Text String

May 28, 2014

How can i find the first 15 letters or numbers in a text string "material change:"

or find ":" then display everything to the left of that symbol ?

assume formula in a1 and test string in b1.

I wish there was a class you could take on this stuff.

View 9 Replies View Related

Counting String Gap/spaces/words

Mar 8, 2007

How can I count the number of gaps (spaces) of a string? (Adding one we have number of words!). I need to save the position of each gap (space) on a array. How can I do it? With InStr()? The variable (string) that i'm working is GlbTargetRange.Value

View 4 Replies View Related

Compare String Of Words To Cell Content

Jan 31, 2008

I need to compare a list of keywords (stored in a string variable) with the content of a cell in order to see if any of the keywords do appear in the cell. I have to work with many such lists which can grow over time. What I'm doing is using InStr(cell with content, keyword) for each keyword. My goal is to have a macro which can automatically create the long formula from any list. My actual problem is that the formula I've build and stored acts more like a string. I'd like it to say either true or false. Anyway, here's a sample of what I came up with so far.

Sub build_formula()
Dim i As Integer, j As Integer, m(20) As Integer, n As Integer, z As Integer
Dim List As String, f(2) As String, s(20) As String, sp As String, Source As String
Dim Formula As Variant
List = "black, blue, green, red, yellow, white, z"
Source = Cells(3, 1).Value
z = InStr(1, List, "z")
i = 1
n = 1
Do
i = InStr(i + 1, List, ",")
m(n) = i ..........................

View 5 Replies View Related

Find List And Count All Words Within A Text String?

Feb 26, 2014

I have uploaded my spreadsheet.

I have a column of text strings on Sheet1, Column A, which I need to check for the presence of keywords listed on Sheet2, Column A

So if any word from the keyword list on Sheet2, Column A is found in, say, cell A2 of Sheet1, the cell to its right (B1) should have a formula to display the count of keywords found in A2. I also would like to see each keyword identified either through a highlight or a list. I need the formula to NOT be case sensitive and the match does not have to be for whole words).

View 2 Replies View Related

Extract Multiple Fixed Format Words From String?

Jun 23, 2014

I need to extract all instances of words that have format xnnnnnn, where x is an alpha character (letter of alphabet, to be precise) and nnnnnn are numbers. The words could something like u435586. The problem is I do not know how many instances of these words are in the string. The entire string is contained in a cell. A sample string could be something like:

SMNTv922970;#1283;#SMNT 433925;#1284;#SQRS 003417;#1285;#SQRSp047683;#1286;#SMNT 6132451;#1287;#SQRSw3145627;#1288

and the end result should be

v922970 t433925 t003417 p047683 t6132451 w3145627

The words are preceded by the character "" which might facilitate the search.

View 2 Replies View Related

Code Is Not Accepting Beginning Of String Values (WORDS)

Jul 14, 2014

This code is not accepting the values which is starting from strings like this

for example

bearing 15/16 IN LG, 1-1/16 IN OD, 11/32 IN THK,21/128 IN ID
19/128 IN LG, 2-3/64 IN OD, 1/2 IN THK, 5/64 IN ID
steel 15/16 IN LG, 1-1/16 IN OD, 11/32 IN THK,21/128 IN ID
19/128 IN LG, 2-3/64 IN OD, 1/2 IN THK, 5/64 IN ID
spares 15/16 IN LG, 1-1/16 IN OD, 11/32 IN THK,21/128 IN ID

[Code] .....

View 1 Replies View Related

Extract 1st 2 Words & Middle Section From Text String

Jun 18, 2008

I have a text string with 3 data to be extracted and then split throu range, but only specific parts is needed.

string is like that:
Name Surname - Number : ################ - Exp : YYMM - Centre etc etc....

Lets say its allways happens in cell A3, for example:
Adrian Kukuwas - Number : 1235467890123456 - Exp: 1009 - Centre... I would like a macro:

*that runs only if string starts with a letter, then if so
*takes Adrian Kukuwas to cell D3
*takes 1009 (the exp) to B3 and makes it look like 09 10 (or 09_10)
*in A3 leaving only the number 1235467890123456 text formated (sometimes the copy source formats the cell for bad)

View 9 Replies View Related

Return True / False If String Of Words Is Present In Another List?

Dec 5, 2013

I have two lists, one that is 99,000 lines and the other is 150. I am trying to find out where the word(s) in the list of 150 is present in the list 99,000.

1st list of 99,000 is in Sheet1 A1-A99000 and the second list is in Sheet2 B1-B150.

The caveat is that in column A there is additional words in that cell so you can not do a simple vlookup, because there may or may not be an exact match.

I have been stuck for hours and the best i can get is to use the match function but it is not working because it is not exact. Last thing i want to do is use the CTRL F key to lookup all the words.

View 2 Replies View Related

Make A Lookup On A String Of Words From One Of My Worksheets And Hyper Link

Oct 9, 2008

I want to make a lookup on a string of words from one of my worksheets and I also whant to make a hyper link so that when I click on the lookup command I should pop to the other worksheet were my database is.

View 2 Replies View Related

Extract Middle Words From Text String With Multiple Dots?

Oct 22, 2012

black.blue.red.yellow.green.white

I'd like to extract just "blue.red.yellow.green" to the following spreadsheet column, keeping the intermediate dots, but getting rid of the first/last words and their succeeding or preceding dots.

I've tried using RIGHT, LEFT, MID formulas unsuccessfully

View 3 Replies View Related

Split Words In Comma Separated String To Cells In Column

Nov 1, 2012

I have extracted a string from my address database which goes like name,address1,address2,city,postcode,country

I need to display in Column B as:

name
address1
address2
city
postcode
country

how to do this using VBA.

View 2 Replies View Related

Changing The Value Of A String

Mar 4, 2008

I have a string that may consist of one, two or three digits. If it has one digit, I want to append two zeroes to the left of it. If it has two digits, I want to append one zero to the left of it. The string is titled "Result". In this case, the original string was "64" and I wanted to change it to "064".

I tried this, to no avail:

If Len(Result) = 2 Then
zero = 0
Result = (zero & Result)
MsgBox ("The new value for result is " & Result)
End If

-----------------------

How can I alter the value of a string depending up its length (in this case)?

If I use a new variable, the transfer of value works okay, as so:

If Len(Result) = 2 Then
zero = 0
Result1 = (zero & Result)
MsgBox ("The new value for result is " & Result1)
End If

-----------------------

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved