重新计算不断变化的单元格背景 [英] Recalculate on changing cell background

问题描述

我将UDF与IF语句结合使用，将"X"放在具有背景色的单元格上，当我更改背景色时，该公式不会重新计算如何使用Private Sub Workbook_SheetChange，因此每次我更改所有公式重新计算的单元格背景颜色.

I have a UDF combined with an IF statement to put an "X" on a cell with a background color, the formula does not recalculate when I change the background color how can I use a Private Sub Workbook_SheetChange so that every time I change a cell background color all formulas recalculate.

公式很简单=IF(CELLCOLOR(M2)="Custom color or no fill","","x"

这是UDF

Function CELLCOLOR(rCell As Range, Optional ColorName As Boolean)
Application.Volatile
Dim strColor As String
Dim iIndexNum As Integer

Select Case rCell.Interior.ColorIndex

Case 1

strColor = "Black"

iIndexNum = 1

Case 53

strColor = "Brown"

iIndexNum = 53

Case 52

strColor = "Olive Green"

iIndexNum = 52

Case 51

strColor = "Dark Green"

iIndexNum = 51

Case 49

strColor = "Dark Teal"

iIndexNum = 49

Case 11

strColor = "Dark Blue"

iIndexNum = 11

Case 55

strColor = "Indigo"

iIndexNum = 55

Case 56

strColor = "Gray-80%"

iIndexNum = 56

Case 9

strColor = "Dark Red"

iIndexNum = 9

Case 46

strColor = "Orange"

iIndexNum = 46

Case 12

strColor = "Dark Yellow"

iIndexNum = 12

Case 10

strColor = "Green"

iIndexNum = 10

Case 14

strColor = "Teal"

iIndexNum = 14

Case 5

strColor = "Blue"

iIndexNum = 5

Case 47

strColor = "Blue-Gray"

iIndexNum = 47

Case 16

strColor = "Gray-50%"

iIndexNum = 16

Case 3

strColor = "Red"

iIndexNum = 3

Case 45

strColor = "Light Orange"

iIndexNum = 45

Case 43

strColor = "Lime"

iIndexNum = 43

Case 50

strColor = "Sea Green"

iIndexNum = 50

Case 42

strColor = "Aqua"

iIndexNum = 42

Case 41

strColor = "Light Blue"

iIndexNum = 41

Case 13

strColor = "Violet"

iIndexNum = 13

Case 48

strColor = "Gray-40%"

iIndexNum = 48

Case 7

strColor = "Pink"

iIndexNum = 7

Case 44

strColor = "Gold"

iIndexNum = 44

Case 6

strColor = "Yellow"

iIndexNum = 6

Case 4

strColor = "Bright Green"

iIndexNum = 4

Case 8

strColor = "Turqoise"

iIndexNum = 8

Case 33

strColor = "Sky Blue"

iIndexNum = 33

Case 54

strColor = "Plum"

iIndexNum = 54

Case 15

strColor = "Gray-25%"

iIndexNum = 15

Case 38

strColor = "Rose"

iIndexNum = 38

Case 40

strColor = "Tan"

iIndexNum = 40

Case 36

strColor = "Light Yellow"

iIndexNum = 36

Case 35

strColor = "Light Green"

iIndexNum = 35

Case 34

strColor = "Light Turqoise"

iIndexNum = 34

Case 37

strColor = "Pale Blue"

iIndexNum = 37

Case 39

strColor = "Lavendar"

iIndexNum = 39

Case 2

strColor = "White"

iIndexNum = 2

Case Else

strColor = "Custom color or no fill"

End Select



If ColorName = False Or strColor = "Custom color or no fill" Then

CELLCOLOR = strColor

Else

CELLCOLOR = iIndexNum

End If


'All credits go to the dude who wrote this code which I'm not
End Function

推荐答案

此代码在sheeet代码模块中(而不是常规模块中)

This code goes in the sheeet code module (not in a regular module)

编辑"A1:D50"以覆盖包含单元格颜色UDF的范围

Edit the "A1:D50" to cover the range containing your cell color UDF

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Me.Range("A1:D50").Calculate
End Sub

这篇关于重新计算不断变化的单元格背景的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！