Excel - UDF That Returns String Of Multiple String Objects / Possible To Color Font?

Sep 19, 2012

I have a udf that returns a string to the cell. The string is made up of multiple string "objects". What I am wondering is if I can set the font color of certain objects so that when the final string is built and returned, the font of those portions is set.

Ex. of simple idea (this is not actually my code, just a way to illustrate. I realize there is no point to this UDF):

VB:

Function StringReturn (Str1 As String, Str2 As String, Str3 As String) As String
StringReturn = Str1 & Str2 & Str3
End Function

Now what if I wanted Str1 and Str3 to be blue, and Str2 to be red for example. So that when the UDF calculates it would return: Str1Str2Str3

View 2 Replies


ADVERTISEMENT

Changing Font Color For String Variable?

Apr 18, 2012

how to or if it's possible to change the font color of a string variable? or is there any workaround?

View 2 Replies View Related

How To Change Font Color In Portion Of Text String

Jul 16, 2013

I have a button (shape) whose text value is linked to cell A1. (In other words, if text in cell A1 changes, the text on the button changes). A concatenation formula exists in cell A1 to "join" together a combination of text and cell values. The formula in A1 looks something like this:

="You have "&B10&" records that contain errors."

I'd like to set the font for the "&B10" portion to be a different color than the rest of the text string so that it stands out from the rest of the text. Obviously this can be done when the text is manually entered into a text box, but when using a formula to populate the text in the text box, it's all or nothing. I'm hoping to find a way to embed a separate font color within the formula string in A1 for just the portion I want to change? For example, all text would be regular/black, but the numeric value generated from cell B10 would be yellow.

View 1 Replies View Related

Formula That Returns Result & Font Color Of Referenced Cell

Apr 3, 2008

I have 2 tables, 1 beneath the other - table 1 columns represent date ranges and their values. Columns and their data are alternating color coding. Table 2 references, by means of array formula, this data - IS it possible to include the font color as a result?

BCDEFGH503/0310/0317/0324/03609/0316/0323/0330/03712348Prod113,91113,69713,58213,4849Prod21,9241,8943,1151,86510Prod31,2601,2401,2301,22121Age (Days)22Prod1211391123Prod22624Prod326

Formula in H22 = '{=SUMPRODUCT(((H$5-$D22)>=$E$5:$O$5)*((H$5-$D22)

View 9 Replies View Related

How To Change Font Of String

Oct 18, 2011

i have a string in a cell, that would say "Your results are -10% for the week". is there any way to change the font to red, for the number, including the minus sign preceeding it, and the percent sign following it. all are in one column, about a thousand rows. the rest can stay black.

View 5 Replies View Related

Changing Font Of A String Variable?

Nov 28, 2012

I'm taking some university classes and one of them is called Applications Programming and we have been using excel VBA. I have a project that I'm working on, and it's very basic. Basically we need to create a userform that has 3 option buttons, 4 check boxes, a couple labels and a couple text boxes. It's suppose to be a ice cream selection terminal. Option boxes for basic flavors of ice cream, check boxes for toppings, one text box for user input of number of scoops of ice cream and the last text box to output a reciept/overview of purchase.

So here's the question... I was wondering if there's a way to change the font of a single string variable, or change the font of a word within a string. My output to the saleBox(My receipt overview) is this:

saleBox.Text = ("Thanks you for your Purchase") & vbCrLf & vbCrLf & _
"You ordered " & numOfScoop & " scoops of " & flavorChoice & "ice cream" & vbCrLf & _ "Topped with: " & toppings & vbCrLf & vbCrLf & total

(not sure how the code is gonna look in the message but I'm sure you get the idea)

i want to be able to change the font for the flavorChoice or toppings variables. They are both strings.

Anyways, I know I can get away with basically what I have there and get full marks, but I like going above what the basics are. If it's kinda complicated.

I'm gonna attach a screenshot, more info is better than not enough right... nevermind its askin for a url!

*** I just now got the idea of putting another textbox over the area that would output the text I want to change and hide the border or something and then change the font of that text box through the properties ***

View 3 Replies View Related

Excel 2007 :: Changing Bar Series Color To Adjacent Cell Font Color

Aug 8, 2012

I am trying to use VBA in Excel 2007 to change bar chart series colors. I have found a few posts that link it to a cell background, but I'm struggling to find one that does the font color.

It would be great if I could change the bar chart series to match the color of the text in the A column, so that if I highlighted the value in A1 and changed the text color to orange for whatever reason, the chart updates the value of 1.2 to an orange bar (see below).

T. A1 B1

Sample 1 1.2

Sample 2 2.1

Sample 3 1.7

Sample 4 5.6

View 3 Replies View Related

Find Method Returns A String Instead Of A Range

Jun 24, 2009

For some reason, it seems that this following line returns the value its supposed to be searching for instead of the range where it finds the value.

View 5 Replies View Related

Print Color Objects But Not Objects That Are Highlighted / Colored For Input And Grids?

Dec 27, 2013

So I'm creating a grid worksheet for engineering calculations and I have a couple questions about the best way to do it. I've been messing with excel for my calcs for about a decade now, and I every once in a while I try and improve them.

First: I will have several input areas that will either be colored text or shaded background (either works for me). I don't want these 'input required' objects to print as color, just black. But I want my logo at the sheet top to print as color. I've only found ways to not print any color. Can I print the logo as color and the 'input required' stuff as black?

Second: When I do calcs by hand, I write them out on 10x10 grid paper. Each 10x10 grid is one inch. In the past I've created this grid out of the cells, which works. I frequently need to change formulas around though, and each time I do this, I end up needing to mess with the grid cells also. Is there a way to create the grid and have it in the background so it doesn't need to be adjusted each time I change formulas? I wan't the grids to print, and also want to see them on the screen, as I sometimes draw simple objects along with the formulas.

View 9 Replies View Related

VB Function To Return CODE128b Barcode Font String ()

Feb 26, 2007

I found what looks to be a BASIC or perhaps early C form of code that will do exactly this, however I am finding it out of my grasp to 'convert' the below code over to VB for Excel, and I was hoping someone would be able to piece it together for me. Code128B is a barcode font that requires a 'computed' start and end bit, as well as a checksum character(s) in order for the barcode to be valid and readable by barcode scanners. This code (in whatever it is written, I don't really know), is said to do just that. Code:..................

View 4 Replies View Related

Macro To Split String Returns Type Mismatch

Aug 5, 2013

My code below returns a type mismatch? It is looking at the values in column B which are formatted as text and the output is in column J. An example of a value is 2.1.15 I want to extract 1 (i.e. the central character between two ".").

VB:
Sub ConvertLineNo()

Dim r As Long, TempStr As String
For r = Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -2
TempStr = Cells(r, "B")
TempStr = Split(TempStr, ".")
Cells(r, "J").Value = TempStr
Next
End Sub

View 3 Replies View Related

Conditional Formula - Value Field Takes Max Of Other Four Columns And Returns String?

Jun 3, 2014

I have a dataset in the following manner

A B C D Value
1 0 2 3 D
2 1 3 4 D
6 2 3 4 A

The VALUE field is my problem. I need a formula so that the value field takes the max of the other four columns and returns a string (e.g. 'A' in the column header)

View 4 Replies View Related

VBA Change Font Size Of A Character Or Letter Within Any String In Selected Cells

May 15, 2009

Looking for VBA that can change the font size of "•" char(0149) within any string in selected cells.

Also looking to delete the last "•" char(0149) within any string of selected cells.

What would the syntax for these two functions be?

View 9 Replies View Related

Extract Number From Multiple Occurrence Delimiter Of String Excel

Aug 14, 2014

I'm trying to extract all the numbers from the left of the delimiter ":" . They can occur once or more.

E.g. cell F2 contains BP2.2.1:40 BP2.2.1:50 BP2.2.1:60 BP2.2.1:70 BP2.4.1:80

and what i want as a result is : BP2.2.1 BP2.2.1 BP2.2.1 BP2.2.1 BP2.4.1.

I've tried =IFERROR(LEFT(F2,FIND(":",F2)-2), " ") but only displays 1 out of a possible 4 in the cell.

View 12 Replies View Related

Excel 2010 :: ODBC Connection String With Multiple Variable (WHERE) Requirements

Dec 29, 2013

With VBA in Excel 2010 I am connecting to a SQL server by using ODBC.

This is a two part process:

Part one:I have created a connection string that gets me the following data: A, B, C, D, E from sheet tpoPurchOrder Where B is equal to "1" And Where C is equal to a changing field under Sheets("Macros").Range("B2")

Here is part one

Code:

Sub Part1()
Sheets("Open PO by Vendor").Select
Sheets("Open PO by Vendor").Cells.Clear
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DSN=Connection;Description=Description;UID=USER;PWD=PASSWORD;APP=Microsoft Office 2010;WSID=Workstation;DATABASE=Database" _
, Destination:=Range("$A$1")).QueryTable

[code]...

Part one works perfectly.

Part two is where I have problems.Part two is a little different because I have the same connection, but what changes is the table that I am looking at "tpoPOLine" instead of "tpoPurchOrder" and the where is now going to have a variable number of commands.

I want part two to be depend on part one. Under part one I want the result from column E to be a where statement in part two.

As follows:

In particular: *E3 from Part 1*, etc.

Code:
"SELECT tpoPOLine.Status, tpoPOLine.POKey, tpoPOLine.ItemKey, tpoPOLine.POLineNo, tpoPOLine.UnitCost, tpoPOLine.ExtAmt" & Chr(13) & "" & Chr(10) & "FROM mas500_DII_app.dbo.tpoPOLine tpoPOLine" & Chr(13) & "" & Chr(10) & "WHERE (tpoPOLine.POKey=*E2 from Part 1*) OR (" _
, _
"tpoPOLine.POKey=*E3 from Part 1* ) OR (tpoPOLine.POKey=*E4 from Part 1*)" & Chr(13) & "" & Chr(10) & "ORDER BY tpoPOLine.POKey" _
)

Now my problem is that sometimes the E column from part one ends up being 1 row, sometimes it ends up being 50 rows. I would like the code to change accordingly.

1. Pull everything from the SQL server filter once in excel. This is not as efficient as the database has ~300,000 rows in the tpoPOline table and would take a lot longer then needed.

2. Create a nested if table and

Change

Code:
WHERE (tpoPOLine.POKey=*E2 from Part 1*) OR (tpoPOLine.POKey=*E3 from Part 1* ) OR (tpoPOLine.POKey=*E4 from Part 1*)"

to the value of that nested if table

The nested if table would be something like =if(isblank(E2),"E1",if(isblank(E3)... etc for ~50 rows. I know the syntax is incorrect but you get my point.

3. Rerun the query for each value in column E. That would require the connection to happen ~50 times which would not be that great, as well as I would have to copy and paste the data after each run as the tables cannot overlap.

What I am looking for is a way to run this only once, on one sheet, without writing a nested if table with 50 if's.

View 5 Replies View Related

Multiple Font Color In Single Cell

Jun 27, 2006

I was wondering if it is possible to have multiple font colors in a single cell.
Such as:

ActiveCell.Value="Red & Blue"

Where the font for the word Red would be Red and Blue would be Blue.

View 3 Replies View Related

If Cell In Col. A Exists As Part Of String In Col.B Then Color It

Dec 29, 2009

i have two columns in my worksheet A and B. Now col. A contains of words and column B contains of long strings. I want to make those parts of col. B which match to the content of A.

For Example It is now:

Col. A...................Col.B
word....................öladföljadfwordpapdfadlk
word2..................öadsfoewordpasdfword2rs

Should be:

Col. A...................Col.B
word....................öladföljadfwordpapdfadlk
word2..................öadsfoewordpasdfword2rs

I am attaching this example as excel file. I will be very thankful if some one could add the formula to this file and upload it.

View 4 Replies View Related

Excel 2007 :: Change Font Color When Point To It

Mar 19, 2014

Using excel 2007. In a A table of marks obtained by a student. I want to get the content of the cell be displayed in a bigger font and in a particular color when i point to it. When I point to a cell content the display should be in a color of my choice say green and the font be greater than 20 pixels.

View 1 Replies View Related

Generating Random Number String Based On Alphanumeric String?

Aug 14, 2014

Wondering if it is possible to generate a random 4 digit number based off an alphanumeric string?

Example;

Cell A1 has 123XVF1234
Cell A2 has 321AFW4321

In B1 I would like to have a 4-6 digit number that is generated based on the alphanumeric data in Cell A1 (and so on down the list). If that is possible, I would also need to be able to convert back the 4-6 digit number back to its original alphanumeric value

Example;

If B1 returns 643562 it would need to be able to be converted back to 123XVF1234

View 7 Replies View Related

Change Current Cell Value If Number String NOT Letter String?

Apr 7, 2014

In sheet1 I have a simple database consisting of 5 columns of data

Column A : Name ie James Jones
Column B : payroll number ie 123456
Column C : shift times ie 1245-2124
Column D : job title ie floor
Column E : comments ie A/L or 0600-1500

what I would like is some code that will go down Column E and if a 'time string' ie 1300-2130 is found then copy this string and paste into corresponding value in column C. If a text string is found ie A/L or Sick or anything like this then ignore and move onto next cell, loop this until all cells in column E have been checked.

View 4 Replies View Related

Find String Of Text Plus Cell Index Color?

May 3, 2013

I am trying to come up with a macro that will search a range for a specific string of text plus Interior.ColorIndex = 4. The range can have the text in it multiple times but i only want the macro to fire the next line of it if the cell address also has that color. Is there a way that can be done? My code below stops after it finds its first occurrence of the string and moves on to the next lookup. How do i make it look for not only the value but the index color?

View 5 Replies View Related

Select Automatic Color Scheme (font) For Excel Sheet

Jan 13, 2014

I want to select an automatic colour scheme (font) for an Exel sheet. It has 3 columns with values. If the coloums are A, B & C I want to mark the higher value in red. If B is higher than A, B should be red. Again if C is also higher than B, then C also should be red... like that. Values should be read row-wise. How can I do this using a formula?

View 1 Replies View Related

Conditional String: Repeat The Same Account Number In The String

Jan 10, 2009

I need to create a string of all the Acc Number but I dun not wan any repeat of the same Acc number in the String . The number of Acc number to be put in the string may varies sometimes. It may varies from 3 to 1000 or more . Is there a way to write a general formula to create such string?

View 4 Replies View Related

Replacing Misspelled Sub-string In Varying Full String

Aug 27, 2009

I have a situation where a word (in this case "Restaurant") is misspelled in a list of about 78,000 location names. The location names are in one column, and the [misspelled] word "Restaurant" is anywhere between the first word of the string, to the last, with any amount of alphanumeric/symbol characters between. For example:

Alice's Restaurant
Alli's Restaurant & Bar
Alexis Restaurant of Waukesha
Amigo's Mexican Restaurant #2

I want to replace any misspellings of the word with correct, but since the list is so long, and the way the word is misspelled varies so much, going through manually is entirely too time-consuming.

Some of the variations I've seen so far are Resta, Restau, Restaur, Restuara, etc...

Is there a way to search and replace cells that contain the text in any location of the string? Specifically, where ever there is JUST "Restau", replace with "Restaurant", regardless of where it is in the string? This way, trailing text is not deleted or manipulated, i.e. "Alice's Restaur and Bar" will change to "Alice's Restaurant and Bar".

View 14 Replies View Related

Excel 2011 :: Find Multiple Text Strings In Another Text String

Mar 8, 2013

How do I use an Excel formula to find which (if any) multiple sets, each of up to 50 words, exist in a series of rows of a spreadsheet - if set A has one or more words found in a searched cell.

A positive result will return a specific value in the designated result cell. If none of the words in Set A is found in the searched cell, the formulae will repeat the test for the words in Set B, and so on.

After all 50 sets of words have been tested, the formula will move to the next cell in the searched column.

New words will be added to the sets of words continually as required.

Multiple words within sets are included in double quotes. Within each set of words there will be some n-tuples of words (i.e. 24 adjacent words) that contain one or more of the words in the set, but for which the formula will be required to return a negative result. Example: Set A = word 1, word2, word 3, "word1 word2 word3". (The words within a set could also be each entered in separate columns, as opposed to all included in a single cell.) The single column of text to be searched is about 10,000 rows.

I am wanting to use the above in a spreadsheet that contains data downloaded from a series of bank accounts to automatically allocate items of expenditure to one of 20 or so different categories of expenditure.

The formula will search the description field to find words that are used in the in the downloaded files from the various accounts to describe each transaction.

If a word describing travel expenditure (e.g. hotel, "holiday inn" but not "holiday travel") is found in the description of an expenditure item - the item cost will be allocated to the TRAVEL EXPENDITURE column, which is one of 20 or so different categories of expenditure.

Happy to consider a different solution if the task can be done better a different way.

Tried using a combination of INDEX/SEARCH/IF in Excel, but was not able to get a correct result. PS I am using Excel 2011 for Mac - which does not allow macros, so the solution needs to be entirely formula based.

View 1 Replies View Related

Excel 2010 :: Color Fill And Font Reset Macro Routine?

Aug 12, 2012

In Excel 2010, you can color a cell with a fill color and a font color.

It can be done manually via one of 3 methods that I know of (aside from a macro or a routine):

-From the ribbon button (underneath the font size and increase and decrease font size toolbar buttons)

-By formatting the cell (right clicking on a cell),

-Clicking on the ribbon's font section (giving you the same 6 category format cell box as right clicking on a cell)

What I need is a routine that resets the Fill Color and Font Color toolbar buttons to "No Fill" and "Automatic" if I run it inside a macro or create a command button on a worksheet.

View 4 Replies View Related

Excel 2007 :: Insert Multiple Objects

Jul 4, 2012

Excel 2007.

Insert - Object allows me to insert a object (display as icon is used). Is it also possible to insert multiple objects in one go? Via this option I can always only select 1 object.

View 1 Replies View Related

VBA To Search String And Insert Row If String Not Found

Apr 11, 2013

I have a spreadsheet which has "Employee: [agent 1 name]" in column A and it may or may not have the word "Break" in the same column before it mentions "Employee: [agent 2 name]". The amount of data between agent 1 and agent 2 varies and am needing code which will insert a row above "Employee: [agent 2 name]" if "Break" is not found, and add the word "Break" in column A on the inserted row. I would need this to loop through the spreadsheet until all 100+ agents have been searched.

I'm also needing this done for the word "Meeting" and would insert a row 2 rows above the next agent.

View 6 Replies View Related

Insert String And String Variable Into Cell

Feb 18, 2014

I'm using a userform to create a new sheet. The form already creates the sheet and names it what was typed into the userform. Now I want it to place that variable in a cell along with a string. the following code will place the variable from the form (tbname) into cell b5.

View 2 Replies View Related

String Extract - Separate First Two Or Three Letters In A String

Sep 4, 2012

I am working with flight numbers and want to split out the letters from the digits. Examples,

BA1234A
BA123
EZY4566H
BE7893B

In column A I need the first two or three letters only,

BA
BA
EZY
BE

In column B I need everything to the right of what appears in column A

1234A
123
4566H
893B
etc.

View 7 Replies View Related







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