# Evaluate Cell Based On Number Format VBA

Nov 2, 2011

I'm trying to insert a random number into the unprotected cells on a worksheet depending on the type of number formatting in the cell. There are 4 basic cases:

Number formatted as percentNumber Formated general with no decimal placesNumber formatted general with two decimal placesNumber formatted in thousands

The code below only contains three criteria, I was trying to get that to work first....Based on this criteria I want to insert a random number of an appropriate size. Please find the vba below.

Sum randomnbr()
Dim F as String

For Each c In ActiveSheet.Range("A1:AK2500")
F = c.numberformat
If c.Locked = False And IsNumeric(c.Value) = True And c.EntireRow.Hidden = False And c.EntireColumn.Hidden = False Then
If F Like "*#,##0*" Then
c.Value = Round(Rnd, 0) * 1000000
If F Like "*%*" Then
c.Value = Round(Rnd, 2)
Else: c.Value = Round(Rnd, 2) * 1000
End If
End Sub

## Format Cell Number Based On Value

Feb 19, 2008

I need to evaluate some offers and for the management I need to follow some strict rules like:

1. € 1.000.000 instead of €1000000. But that's not the problem
2. to use maximum 3 digits... Example: 10,2 or 6.12

I tried with an if function and then round, but I think that's not right way. If number < 10, then round with 2 decimal places else, round with 1 decimal place

## Change Number Format Based On Another Cell?

Sep 18, 2009

I have used this forum periodically and it has saved my bacon on numerous occasions so I know someone can help with this!

I created a workbook for staff planning and it's becoming a behemoth!

I'm wanting a range of cells within a row to change the number format to "[hh]:mm" based on a value in the 2nd column of that row. I have played about with a few things and can't seem to get it to work. What I have so far is this:

## Cell Number Format Based On Another Cells Value

Oct 21, 2007

If Cell A1 in my spreadsheet is 1, then cell A2 should appear formatted as a percentage. So if a user enters 9000 into cell A2, it should appear as 9000%. If Cell A1 in my spreadsheet is 2, then cell A2 should appear as a comma-formatted whole number. So if a user enters 9000 into cell A2, it should appear as 9,000. Conditional formatting doesn't appear to let me do this.

## Conditionally Change Number Format Based On Another Cell's Value

Dec 1, 2009

Format the number in Column AL to '[hh]:mm' when Column C's value is ="P/T", otherwise format to 'General'

Column C's value is referenced from another sheet in the same workbook via a VLOOKUP function.

Column AL's value is based on an IF formula which goes like this:
=IF(\$C4="F/T",SUM(\$AJ4)+(\$AK4/2),SUM(\$D4:\$AH4))

Basically the above formula asks if Column C's value is F/T then count (because if F/T then the corresponding values in that row are whole numbers). If not, then SUM (because if P/T the corresponding values in that row are set to [hh]:mm format).

I have another sheet in the same workbook which has code (quoted below) which does something similar but I don't understand it enough to get it doing what I want for the sheet in question.

## Conditional Number Format Based On The Value Of A Specific Cell

May 9, 2009

I have a large spreadsheet which includes many rows whose number format needs to change depending on the value of a specific cell.

So for example:

If cell B20 has value "A", cells Q20:AG20 should display as 1,234
If cell B20 has value "B", cells Q20:AG20 should display as 5.6%
If cell B20 has value "C", cells Q20:AG20 should display as 7.89
Etc...

As conditional formatting does not seem to include a number format option I've worked out that I need some sort of macro to change these settings for me, either automatically whenever the value of B20 changes or, more likely, when I manually choose to run the macro.

## Conditional Format X Number Of Cells Based On A Single Cell?

May 4, 2014

I am looking for a method to conditionally format a variable number of cells below a single cell into which data is entered.

For example ABC = 6, DEF = 12. Therefore, if I enter ABC into D1 then D1:D6 should be conditionally formatted.

## Format Row Cell Color Based On Number Of Text Strings In A Column

Jul 29, 2013

I have been trying to format the rows on this sheet to color scale red based on the number of repeat text strings in Column E. Referring to the attached example sheet, '321/312.2/321.3' appears the most times and the goal is to color code the rows it appears in the deepest shade of red, then the next highest occurring string would shade the rows it appears in a lighter shade, etc, in descending order. Our team currently does this across multiple sheets manually everyday and it would be a real time saver if we could get excel to do this automatically.

## Evaluate A 'dotted' Number Using Formulae (not UDF)

Apr 12, 2006

Is there any way to 'evaluate' a dotted number (similar to an IPv4
address but not just those) using standard worksheet formulae (ie no
UDFs, VBA, or ATP functions)?

To make it a little more complicated, I need to be able to evaluate
not just IP addresses (although that would be a use) but also a
'number' such as 9.0.2 compared to 8.7.5 so that they can be sorted
and / or compared.

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:

1bupdvc02@sneakemail.com

This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb

## Index Function (isolate One Number At A Time And Evaluate Usage)

Mar 27, 2009

The value that is returned is off by 2 rows everytime. When I evaluate the formula, it shows the correct row just before the indexing function does it's thing.

I have a cell phone bill for 20 or so phones and am trying to isolate one number at a time and evaluate usage. The first sheet is my data, the second is sheet ("Breakdown") is where I enter the number in A2 that I want to look at. When I do, it misses the first 2 rows and picks up 2 extra from the following phone number.

## Cell Number Format Equal To Another Cell Number Format?

Oct 20, 2013

I have data in cell A1 that often changes its number format(from \$ to %), can i have the number format change in cell B1 whenever the number format in cell A1 changes?

## Change Cell Format Based On Existing Currency Format

Jun 12, 2008

I would like to have a single button that changes a range of cells from the USD to EURO to perhaps CAD symbol. Can this operation be performed, such that if I start in dollars, and I click the button once, it shifts by range to EURO (not formulas...just symbol)...and then if I click the same button again, it goes to CAD, and then back to USD with a third click?

## Number Format Based On Corresponding Cells Text

May 15, 2008

Cell L90 to L120 are input cells where I type " Percentage" or "Dollar Amount". I need the five cells to the right of each input cell starting two cells over, to be formatted accordingly. I read a thread similar to what I'm asking for suggesting a worksheet change macro, but I can't quite figure it out for a range of cells. Also, in case I need to add a row to my sheet before all of these cells, how can I make sure that this will still work without having to go into the code. Is this an activecell thing?

## Conditional Format Based On Formula Result Being A Whole Number

Dec 6, 2012

I'm looking to conditional format a cell/cells based on whether a formula result returns a whole number or not.

I don't want to include the formula in the sheet itself, just have that as the formula in the condition.

The formula will be along the lines of:

=IF(SUM(BB10/BA10)"a whole number",TRUE,FALSE)

My problem is, is that I don't know who to refer to "a whole number" in Excel formula language.

## Evaluate Each Row And If The Last Value For That Row Is X, Then Highlight That Cell

Oct 8, 2008

I want to do it evaluate each row and if the last value for that row is X, then highlight that cell and the Server name for that row.

I have used conditional formatting but it seems that I cannot specify an option to look at the last cell in a row, evaluate that cell's value and then apply the formatting. I can only have conditional formatting highlight every cell in that row that matches the condition.

I only want the last cell, which represents the most current data, to be highlighted, as well as the category name, if the condition is met.

## Evaluate One Cell Against An Array

Oct 29, 2009

I have two sets of information, on one hand I have telephone numbers and in the other set I have prefixes and countries. My goal is to tell to which country each number belongs:

i.e.

Numbers Prefix Country
4476324125 44 UK
3354326544 33 France
9713425432 971 UAE
9143253245 91 India
1343543253 1 USA
4432412412
4924241214
7431242424

So I would need to add a column next to "Numbers" saying to which country each number belongs.

My list has a few thousand numbers and a couple of hundred prefixes.
I tried with some array formula, but cannot make it work.

## Evaluate Cell Value Then Copy Paste

Apr 14, 2009

I have a column in excel and I want to evaluate each cell in that column and if the cell.value = a specified value I would like for it to copy and paste it in the cell to the left. Below is the code I am using but it isn't working.

## Evaluate A Text String In A Cell

Apr 3, 2008

I want to be able to use a macro to read cells from a certain column so that it will evaluate a text string untill it hits a blank space, then copy the text that it found. I'd like it to work on Excel 2003 or later

## Evaluate Multiple Functions In The 1 Cell

Oct 26, 2007

When using watch window to watch a formula that has multiple formula's in it, is there a way to see the result of 1 formula within the big formula?

E.g. say you have:

= SUMIF(A1:A11,"207",B1:B11)/COUNTIF(A1:A11,"207")

And you put a watch on that, but in watch window it shows the result of the whole formula, say if you want to watch only the SUMIF part?

Im using Excel 2003 by the way.

## Evaluate UserForm Control Where Name Is In 1 Cell & Property In Another

Jan 29, 2009

In A1, other code inserts the name of a Form control (textBox, comboBox etc), and in B1 it inserts the name of a Property that is always valid for the A1 control. I want to concatenate these two items to produce a formula in C1 that evaluates the current value of the Property on the running form and continues to update with each recalc. It's ok if I have to force a recalc to get the latest values.

The code feeding the items to A1 and B1 and which will be harvesting the Property values from C1 is running in the same Form that holds the controls being referenced.
So if A1= "Label1" and B1 = "BackColor", then in C1 I'd like the same result as if in VBA I said X = Label1.backcolor. I think that what I need is the Evaluate function, but I've read Arron's article on it I just can't seem to make it work here.

## Change Format Of Cell Into Number?

Dec 15, 2013

I am having slight trouble with the formatting of some cells. Every month I will be copying data into a row, the problem is Excel thinks it's a date instead of an integer of some sort.

The value copied will look something like this:
79:42:00
(meaning 79 hours and 42 minutes)

Excel is taking that and interpreting it as:
1/3/1900 7:42:00 AM

Is there an easy way to format this so Excel thinks it's a number (even if 79.7).

I tried to change the format of the cell to "Text", but it changes it to: 3.32083333333333

## Number Custom Format A Cell

May 31, 2009

is it possible to custom format a cell in order to:

a) Present 3.33333 as 3.33 (max. of 2 decimal digits or less)
b) Present 3.333 as 3.33
c) Present 3.33 as 3.33
d) Present 3.3 as 3.3
e) Present 3 as 3 (NO zeros and NO decimal dot)

## Format A Cell That Causes Sum Calculation To Go Over A Pre-set Number

Jul 31, 2009

I'm working with Loan amounts and i need to know how to set a excel spread sheet to conditionally format a cell that causes a sum formula to exceed a preset limit.

Example ...

## Changing Cell Number Format With VBA

Jul 27, 2012

I believe Range.Numbeformat is the property.

I have a cell with a dropdown picker. If the user chooses "Distractor" from the picker, Worksheet_Change triggers an Inputbox to open, asking what kind of distractor it is. We'll use "Gloves" for this example.

I want the cell to display "Distractor - Gloves" while keeping the actual value as "Distractor"

The following is not working for me:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim DistractorText As String
Case "\$B\$23"
If Target.Value = "Distractor" Then
DistractorText = InputBox("Type of Distractor:")
Target.NumberFormat = "@" & " - " & DistractorText

There's more code, but I copy-pasted the trouble area. The last line using the NumberFormat property is giving me trouble.

## Unable To Format Number Within Cell

Jul 25, 2008

Working on matching 2 column of numbers.

Unable to match as 1 column has format where number has a space in front of first character.

_425638 as opposed to
425638

Not an underscore tough

Have tried text to columns - have tried editing cell and replacing space with nothing via edit/replace but no joy only way appears to be entering cell and deleting manually - there are thousands to update.

## Format Number & Text In Same Cell

Jun 28, 2007

i have the next formula

=IF(C6="PRODUCT 1","\$"&VLOOKUP(B6,N:T,3,FALSE)&" & "&"\$"&VLOOKUP(B6,N:T,5,FALSE),"N/A”)

which aberrantly returns text:

\$24.675 & \$26.2

I was wandering is there a way to format or put a formula which will format the numbers to be with two decimal places. The cell containing the formula is already formatted as number and the source tables are numbers with two decimal places.

i want the result to look like this
\$24.68 & \$26.20

## Subscript Macro: Go Cell By Cell And Format The Last Number

Apr 2, 2008

Is it possible to make a macro that will go cell by cell and format the last number of the cell as subscript? Here's an example:

Brooks #1H 5Stewart #8H, #9H 8Stevenson D Unit #1H 2150,000 BblsStewart #8H, #9H 8Stevenson D Unit #1H 2Brooks #1H 5Stewart #8H, #9H 8Holasek #1H 5Brooks #1H 5Stewart #6H, #7H 8140,000 BblsRayburn Unit #5H, #6H, #12H 6160,000 BblsHolasek #1H 5450,000 BblsStewart #6H, #7H 8#N/ARayburn Unit #5H, #6H, #12H 6Stewart #6H, #7H 8Holasek #1H 5Rayburn Unit #5H, #6H, #12H 6Stewart #6H, #7H 8Holasek #1H 5

I would like ONLY the last NUMBER of the cell to be formatted as subscript. The cells with "Bbls" would be left as is.

## Format Cell To Show % As The Number Type-in

May 1, 2014

(3) examples when I type a number to a cell:
If I type a number "1", I want that cell to show 1.000% but not 100.000%.
If I type a number ".2", I want that cell to show 0.200% but not 20.000%.
If I type a number "25.5", I want that cell to show 0.255% but not 2550.000%.

I play around with the below custom format cells but they do not work.
_0.01*#.000%
_0.01*#,.000%
0.01*#,.000%

I could use two cells one for number, other cell has a % mark. But I rather to format a single cell if it is possible.

## Change Cell Format Depending On Number

Apr 16, 2014

I have an if statement which shows either GP% or average revenue per product. The problem is that GP% needs to be in a percentage format and revenue per product needs to be in number format. 500 is now shown as 50000% or 50% will be shown as 0.5 for example. Is there anyway on changing the format of a cell based on the number?

## Format Date Cell With Week Number

Jan 8, 2009

just wondering if its possible to format a cell to display date and week number and if so how to go about it

eg

04/01/09 week 1