Is there a non-VB conditional formula syntax to change the custom number formatting of a cell based on an external cell value. i.e.
if (A4 = 16) custom number format in cell C1 becomes #??/16
if (A4 = 8) custom number format in cell C1 becomes #??/8
if (A4 = 4) custom number format in cell C1 becomes #??/4
??
Thanks.
Hi there everybody.
Here is the situation.
I have a spreadsheet that in particular cells I have assigned a Formula via VBA coding. I managed to have the sheet calculated every sec, so the value of those cells changes every sec.
Now my problem is this.
I want to Conditional Format those cells (which are in Number Format) to a Custom Format I made (no font or color formatting, its a custom Date/Time format of type "ss \s","mm \m ss \s" etc). I need this to be done via VBA code because those cells get deleted and reassigned due to user updating procedure.
When I tried to record my actions, I got a macro with "ExecuteExcel4Macro" in it, which of course doesn't work when trying running the macro.
This is the macro Excel recorded:
Range("C11:D22").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=0,007" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
ExecuteExcel4Macro "(2,1,"" ss s"")"
Selection.FormatConditions(1).StopIfTrue = True
Does anybody has an idea how to solve this? I googled the entire Internet and found nothing helpful.
Can I customize my own set of conditional formatting icon in Excel 2007? If so, how?
thanks in advance.
Hello,
I am running Excel 2003 and I wish to apply conditional formatting to only cells with format Number. Does anyone knows an easy way of doing this or a VBA code for it?
I have tried on my own to write a code , but as I am new to VBA , I can't figure out how to make it work . The code supposed to compare all the numeric values from an uknown number of columns with numeric values from column B row by row and Bold the ones greater. The number of columns and rows is uknown because the data will be exported from another software to Excel , so bassically I wish to format a woorkbook to automatically do that.
The code is below:
VB:
Sub ConvertsFontStyletoBold()
Dim Count As Integer
Dim Count1 As Integer
Dim nr1 As Range
Dim nr2 As Range
Dim nr3 As Range
Dim nr4 As Range
Dim nr5 As Range
Dim nr6 As Range
Dim nr7 As Range
Dim nr8 As Range
Set nr1 = Range("A65536").End(xlUp)
nr1.Select
Set nr2 = Range("a1").End(xlToRight)
nr2.Select
Count = Range("A1", nr2).Columns.Count - 1
Set nr3 = Range("A1", nr1.Offset(0, Count))
nr3.Name = "Data"
nr3.Select
Set nr4 = Range("B65536").End(xlUp)
nr4.Select
Set nr5 = Range("B7", nr4)
nr5.Select
Set nr6 = Range("D7").End(xlToRight)
nr6.Select
Set nr7 = Range("D65536").End(xlUp)
nr7.Select
Count = Range("D7", nr6).Columns.Count - 1
Set nr8 = Range("D7", nr7.Offset(0, Count))
nr8.Name = "Values"
nr8.Select
For Each r In nr8.Rows
i = r.Row
r.Select
If IsNumeric(Cells(r)) = True And r.xlCellValue > nr5.xlCellValue = True Then
cell.Font.Bold = True
End If
Next r
End Sub
If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines
Thank you very much for any help received.
How do you apply custom number formatting to an & formula - i.e. ="Total left to collect is "&A2
I want the content of A2 which is a % to show as a %- any way of doing this as part of the formula?
Hi,
I can do the following using a helper cell in conjunction with IF formula
but I dont/cannot do it that way for the present purpose.
In B4 if a cell displays zero it should display as -> NULL - but if it is
some number like -> 32,567 - then it should display 32,567 as it is.
Can this be achieved through custom formatting. Please suggest.
Please note B4 contains dollar amounts.
--
Thanks a lot,
Hari
India
My fire department uses an Excel spreadsheet to log call information all year long. I have been struggling to come up with a number format that will restrict entry of the box areas (incident location in general) we respond to.
An example of a box area might be: 28-4 or 6-6 or 16-12 or 8-11.
What I want to do is restict this column so that a 4-digit number must be entered each time. This would automatically format the 6-6 box into 06-06, or the 28-4 box into 28-04, or the 8-11 box into 08-11.
I'm not sure if Excel is capable of accomplishing this format. Another solution would be for Excel to reject any entry that is not in the format of ##-## and then force the user to re-enter the box area in the proper format.
Any help would be greatly appreciated! Us fireman aren't the brightest computer guys! Thanks!
-Joe Chronowski
Baltimore, MD
Hi all,
I am interested in having my excel sheet reflect my prices in /32's so i did the format that i saw while browsing the interenet which is "# ??/32" The issue arrives when the number above in the equation is not a fixed number. So for example:
Price(Decimal) | Price(32's) | Excel Output
97.484375 97-15.5/32 97-16/32 ====> So thats a pretty big issue
97.53125 97-17/32 97-17/32 ====> Only work when number is not a decimal...
Keep in mind....the output still needs to be a value/number since calculations will run from it.
So thats is my issue, hope somebody can help me out. Thanks a lot!
Ferrari_99
Hello again everyone, I've been trying to get some numbers imported in from a text document to show up properly and be able to be manipulated with formulae in excel.
It seems to work fine until you get to a certain number of digits then it wont recognize them as numbers anymore. For example:
Code:
961:12
1:00
452:35
38:45
947:35
3:45
5:00
373:51
157:00
2387:15
445:00
2:15
19275:13
0:27
531:37
408:54
496:11
164:04
0:30
671:15
1732:52
32:10
366:40
2525:28
1038:20
As you can see they are measurements of time, specifically in Hours and minutes, the seconds are always zero. The problem arises when you get to the numbers like that 19,000 in the middle. It doesnt recognize that like it does the others and any formulae involving a number in the tens of thousands or above is regarded as text for some reason. I've been playing with the custom number formatting trying to get it into a format that excel understands but have not been able to do so yet. Anyone have any ideas?
Hi guys,
I have a bunch of big numbers 1'000'000, 900'000 etc... and would like to
format them in order to cut last 3 zeros (for chart visualisation purposes).
I suppose that the adequate formatting could be something like #.##/1000,
but this one is not working..
Could you pls help me on this?
Thanx,
