Macro for Change cell color based on active cell absolute value

[Ghoghnuse]

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 .

 

[MARK858]

If your column is column A and you have no headers then maybe

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

 

[Ghoghnuse]

If your column is column A and you have no headers then maybe

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.

 

[MARK858]

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 ?