0
votes

I need some help with a function that can change the color of the cell based on if the value of the cell is equal to the value of another, currently the cell holds an =COUNT(##:##) and I need to to Change the color from red to green if that value is equal to the value in another cell.

With my limited knowledge of VBA and excel I came up with this

Function ChangeColor(CellColor As Range)
Application.Volatile True
If CellColor = cell.Value Then ChangeColor = cell.Interior.ColorIndex = 14
End Function

I would rather not use Conditional Formatting if at all possible, but I am open to it as a last resort if needed. Thanks for all your help and for helping me with previous questions this community is great.

2
A function called from worksheet can't make any changes to the sheet, so you're stuck with conditional formatting or handling the worksheet_calculate event.Tim Williams
And if you did manage to do that in worksheet_calculate or some other means, every time it runs it will clear your Undo history. Since you're marking this Volatile, you're pretty much going to be working without an undo stack.Ashley Pillay
Is it just a single cell? or multiple cells in a specific place (like a "totals" column)? because you could use the worksheet_change event to watch for changes to those specific cells. It does sound like a job for conditional formatting though.NickSlash

2 Answers

1
votes

I did the code below to highlight cells in a column that were the same. The conditional formatting didn't let me cascade the formula of "equal to the cell above", maybe I just didn't do that right. Anyway this worked and is easy to change around and add to it. This example could easily be changed to suit your problem.

Sub checkduplicates()
Dim Loop1 As Integer
Dim Loop1StartRow As Integer
Dim Loop1EndRow As Integer
Dim Loop1Count As Integer
Dim Current As Integer
Dim NextOne As Integer

Loop1StartRow = 4 '4 HYIDAS
Loop1EndRow = 330 '330 HYIDAS
Loop1Count = 0

For Loop1 = Loop1StartRow To Loop1EndRow
    Worksheets("HYIDAS").Activate
    Loop1Count = Loop1Count + 1
    Current = Range("H" & Loop1)
    NextOne = Range("H" & Loop1 + 1)
    If Current = NextOne Then
        Range("H" & Loop1).Interior.Color = 220
        Range("H" & Loop1 + 1).Interior.Color = 220
    End If
Next Loop1
End Sub
0
votes

I think you just using Conditional Formatting I know of function can't change the color or you use macro

If CellColor = Selection.Value Then CellColor.Interior.ColorIndex = 14

i'm so sorry that's i know