Excel MT142 p1: Custom Number Format & Conditional Formatting for Dates & Weekends

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.

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.

I currently have the problem solved in Excel 2007, but my User will be using 2002, and its telling me my solution isnt compatible.

I have one textbox which needs to be formatted to 10 digits so the precededing zeros will show up.
How can I do this so when the textbox is output to Sheet1, the cell it ends up in will retain the custom number format

example:
0847328397

Hello,

I am trying to create a number format for formatting resistor value in their most commonly displayed form. For example 1,200 ohms would be displayed as 1.2kΩ and 5,000,000 ohms would be displayed as 5MΩ, and finally 5 ohms would be displayed as 5Ω.

I have been able to create a format that formats any 2 contiguous formats but not all three.

for example: [>999999]###.0,,ΜΩ;[<1000000]###.0,kΩ;
or: [>999]###.0,kΩ;[<1000]###Ω

both work.(other similar criteria work as well) But if I try to put 3 conditions describing the desired formatting I get and error (containing no useful info) as I try to close the formatting dialog.

Is there a limit to the number of conditions that can be used. Or something else I'm overlooking.

Any help would be greatly appreciated.

rbailets

I am working to setup what I believe may be a Custom Conditional Number Format in Excel 2003 but can't quite complete it.

I am attempting to set a condition in B1 based upon a specific text value in A1 indicating the number value in B1 is negative.


If:
In A1: the value is = C

Then value that is input into B1: automatically changes to a negative number displayed in parenthesis, (100) in red

A B
1 C 100 (manually input) Result per Condition would show as (100) in red


If anyone has any ideas I would appreciate it, thanks.

Hi,

I'm trying to format a cell as a percent without showing the percent sign. The real value of the underlying cell, for example, is "0.25" and I want to show that as "25"

I've been playing with the Excel number format but I can't figure out how to format the cell as a percentage (multiply by 100) but not show the percent sign.

Thanks for any help in advance!

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 All

I need some urgent help. I am trying to design a spreadsheet that work with approval dates.

Basically it has to work as follows:

I have a column containing the date paper work is completed.
I have a column containing the date the deal is approved.

I would like in the third column to show the amount of dyas it took to get the approval e.g date received less date approved.

Now the number of days has to exclude weekend days.

Please help

Thanks

Good day all, is there a way to apply conditional formating to a date range so that the weekends are formatted in one colour and the week days in another. Like for instance below.
Sheet1

* A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE 1 1-Dec-09 2-Dec-09 3-Dec-09 4-Dec-09 5-Dec-09 6-Dec-09 7-Dec-09 8-Dec-09 9-Dec-09 10-Dec-09 11-Dec-09 12-Dec-09 13-Dec-09 14-Dec-09 15-Dec-09 16-Dec-09 17-Dec-09 18-Dec-09 19-Dec-09 20-Dec-09 21-Dec-09 22-Dec-09 23-Dec-09 24-Dec-09 25-Dec-09 26-Dec-09 27-Dec-09 28-Dec-09 29-Dec-09 30-Dec-09 31-Dec-09
Spreadsheet Formulas Cell Formula B1 =A1+1 C1 =B1+1 D1 =C1+1 E1 =D1+1 F1 =E1+1 G1 =F1+1 H1 =G1+1 I1 =H1+1 J1 =I1+1 K1 =J1+1 L1 =K1+1 M1 =L1+1 N1 =M1+1 O1 =N1+1 P1 =O1+1 Q1 =P1+1 R1 =Q1+1 S1 =R1+1 T1 =S1+1 U1 =T1+1 V1 =U1+1 W1 =V1+1 X1 =W1+1 Y1 =X1+1 Z1 =Y1+1 AA1 =Z1+1 AB1 =AA1+1 AC1 =AB1+1 AD1 =AC1+1 AE1 =AD1+1

Excel tables to the web >> Excel Jeanie HTML 4

I have seen some posts on the above but do not clearly understand how it is working. I have a range of cells in a5:a15 named Holidays. I have time sheets that record from the 1st to the 15th or the 16th to the last day of the month in range D4:S4 and need each column range i.e. D4:D20 to identify weekends and holidays. I got the weekends figured out i.e. =OR(WEEKDAY(D$4)=1,WEEKDAY(D$4)=7). How do I get the cells to indicate another color for the Holidays?