excel - Change tab colour based on cell value, runtime error
问题描述
On each tab of my workbook, cell I1 calculates Print if the tab should be printed (meets criteria)
I am trying to code each tab to look at I1 and change tab colour if I1 = "Print"
. All of the codes that I am trying end up resulting in a Runtime error 1004
.
Can anyone see what is wrong with my code?
EDIT 1: The error is thrown up at
.Color = RGB(255, 255, 102)
EDIT 2: I have switched to Calculate from Change. Thanks for that.
EDIT 3: I have two module codes in the same book which password protect/unprotect all tabs. Could this be interfering?
Sub protect_all_sheets()
top:
pass = InputBox("password?")
repass = InputBox("Verify Password")
If Not (pass = repass) Then
MsgBox "you made a boo boo"
GoTo top
End If
For i = 1 To Worksheets.Count
If Worksheets(i).ProtectContents = True Then GoTo oops
Next
For Each s In ActiveWorkbook.Worksheets
s.Protect Password:=pass
Next
Exit Sub
oops: MsgBox "I think you have some sheets that are already protected. Please unprotect all sheets then running this Macro."
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
MyVal = Range("I1").Text
With ActiveSheet.Tab
Select Case MyVal
Case "Print"
.Color = RGB(255, 255, 102)
Case Else
.ColorIndex = xlColorIndexNone
End Select
End With
End Sub
解决方案
Worksheet_Change
不是由计算触发的:你需要Worksheet_Calculate
Private Sub Worksheet_Calculate()
Me.tab.color = IIf(Me.Range("I1").value="Print",vbRed,vbWhite)
End Sub
推荐阅读
- unity3d - 当我更改材质时,Unity 发射没有更新
- html - 从 PDF 中的 HTML 视图将图像打印为背景
- r - 提高 PLS-SEM 中的 Tucker-Lewis 指数 (TLI)
- python - 无法找出如何将 datetime dtype 指定为 Numba @guvectorize
- javascript - CORS 错误是否与速率限制有关?
- spring-boot - AWS CodeStar 代码未传播到网站
- python - 使用 Pandas 数据帧值作为参数的 Python 函数
- javascript - 如何遍历二维数组并过滤数组中的单个值
- php - PHP数组按项目数排序
- python - plt.pcolormesh() 如何可视化 3D numpy 数组?