Macro for Change cell color based on active cell absolute value

Ghoghnuse

New Member
Joined
May 4, 2017
Messages
12
Hello

I have a column of duplicate values Like this :

100
200
-100
50
80
-100

and want a code to change cell fill color based on active cell absolute value.

It means when i select cell with value "100" and run mucro , the fill color of all cells with 100 and -100 values change .

Thanks in advane .:confused:
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
If your column is column A and you have no headers then maybe

Code:
Sub ColActive()
    Dim myCell As Range
    Application.ScreenUpdating = False
    For Each myCell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        If Abs(myCell.Value) = Abs(ActiveCell.Value) Then myCell.Interior.ColorIndex = 6
    Next
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
If your column is column A and you have no headers then maybe

Code:
Sub ColActive()
    Dim myCell As Range
    Application.ScreenUpdating = False
    For Each myCell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        If Abs(myCell.Value) = Abs(ActiveCell.Value) Then myCell.Interior.ColorIndex = 6
    Next
    Application.ScreenUpdating = True
End Sub

Thanks a lot .

How Change this code that :

- Change values in active column ( the column that active cell is ) and not only column a ( or any fix column in macro) ?

- each time generate a random color ?

- if round(abs(active.cell,-3)) equal to round(abs(active.cell,-3)) of any other cells in column , then change the fill color ?

Many thanks.
 
Upvote 0
I'll see if I can look at your additional questions tomorrow but please in future ask your question in full at the start as it often needs a total rewrite of the code and so wastes the respondents time.

Btw, the below question doesn't make any sense as ABS doesn't have a 2nd parameter, please can you word what your requirement is.

- if round(abs(active.cell,-3)) equal to round(abs(active.cell,-3)) of any other cells in column , then change the fill color ?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top