Home » Forums » Open Source Learning Center » Question and Answer

Excel Macro

Submitted by Sorin on 22 March, 2013 - 05:47

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......

 

‹ Pivot table showing two different results when opened on different computers Create a graphic with information from many sheets ›
»
Almir's picture

No need for a macro

Submitted by Almir on 18 April, 2013 - 09:40.

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

Submitted by Sorin on 18 April, 2013 - 10:35.

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

Submitted by Almir on 18 April, 2013 - 10:47.

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

»

Excel Macro

Submitted by Sorin on 18 April, 2013 - 11:04.

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

»
Almir's picture

You don't need a macro

Submitted by Almir on 18 April, 2013 - 08:31.

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

»