Click here to Skip to main content
15,176,823 members
Please Sign up or sign in to vote.
1.00/5 (3 votes)
See more:
Hola. Tengo una base de datos:
A        B          C
1  Numero	Fecha	   Venta
2  1	   10/01/2020	1000
3  $1.00   10/01/2020	500
4  $1.00   15/01/2020	1000
5  2	   10/01/2020	200
6  $2.00   15/01/2020	1000
7  3	   10/01/2020	2000
8  $3.00   10/01/2020	3000
9  $3.00   15/01/2020	500

This database has a conditional formatting applied:
New Rule
Use a formula "$A2 = $A1"
Format to apply: "Number" "Currency"
Applies to cells: $A$2:$A$9
With this conditional formatting rule, the "Currency" format is applied to the "Number" field when its value is equal to that of the previous row.
What I need is to apply this conditional formatting using a macro. I tried with the macro recorder, to run the macro that is recorded, but when trying to run this recorded macro, it gives an error because it does not apply the conditional format.

What I have tried:

The macro (recorded) is as follows:
Sub venta7()
  Selection.NumberFormat = "$ #,##0.00"
  Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A2=$A1"
  ExecuteExcel4Macro "(2,1,""$ #,##0.00"")"
  Selection.FormatConditions(1).StopIfTrue = False
End Sub

When trying to run it, it gives ERROR '1004' at runtime.
This error occurs in the statement:
ExecuteExcel4Macro "(2,1,""$ #,##0.00"")"

I hope someone can help to be able to write the code that allows me to apply the conditional formatting. Thanks

[Translated by MaciejLos via Google Translator]
Updated 22-Nov-21 21:03pm
BillWoodruff 22-Nov-21 23:22pm
please translate to English, and tag your question with appropriate Tags, like Excel.

1 solution

The error message is quite obvious. Application.ExecuteExcel4Macro accepts one parameter - a Microsoft Excel 4.0 macro language function without the equal sign.

For example, to run the macro function =MID("sometext",1,4), String would have to be "MID(""sometext"",1,4)".

In your case, the name of macro4 function has been deleted.

See: Application.ExecuteExcel4Macro method (Excel) | Microsoft Docs[^]

I'd suggest to NOT use Macro4, but add conditional formatting via "normal" VBA code. See: VBA Conditional Formatting | Apply Conditional Format using VBA Excel[^]

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900