Excel Macro

Look for a macro to perform conditional formatting of cells within a range(ex. fill color or bold) that contain closest value to a multiple of a given number. Ex. (for multiples of 100): 88, 97, 105, 170, 188, 210, 277, 294, 303......

 

Almir's picture

No need for a macro

Add two columns: "Closest Hundred" and "Absolute Difference" (Columns B and C).
In A2 (Column "Number") there is number 88, in A3 there is 97 and so on.
In B2 enter the following formula: =ABS(MROUND(A2;100)).
In C2 enter the following formula: =ABS(MROUND(A2;100)-A2)
Copy formulas down.
Supposing your data finish at row 10, select your numbers in column A, choose Conditional Formatting, and paste the following formula:
=ABS(MROUND(A2;100)-A2)=MIN(IF($B$2:$B$10=B2;$C$2:$C$10;""))

Excel Macro

Thanks a lot !! It works great. Now I am trying to count the conditional formatted cells (colored), I see that it is feasible with VBA macros only.

Almir's picture

Use COUNTIF function

Why don't use COUNTIF function with conditional formatting parameter as parameter for counting?

Excel Macro

I do, however I looked for a more elegant and straightforward way.

Almir's picture

You don't need a macro

I have a formula solution for conditional formatting. I will send it you in half an hour.