作业帮excel怎么按照ABCD……的顺序依次为文字首字母为所在的行填不同的背景,首字母一样的填相同色.
来源：学生作业帮 编辑：作业帮 分类：综合作业 时间：2024/05/05 11:09:41
excel怎么按照ABCD……的顺序依次为文字首字母为所在的行填不同的背景,首字母一样的填相同色.
以某列里的文字的首字母从A-Z排序,并把所有首字母为A的行填一种颜色,B的填另一种颜色,以此类推,直到Z,每个字母代表的颜色不一样.
另外,如果要每个字母代表的颜色交替出现呢?重点是填颜色哦= =
不好意思,TUT,请你在回答啊一次吧TUT,直接把代码写上吧TUT
悬赏依旧50TUT
以某列里的文字的首字母从A-Z排序,并把所有首字母为A的行填一种颜色,B的填另一种颜色,以此类推,直到Z,每个字母代表的颜色不一样.
另外,如果要每个字母代表的颜色交替出现呢?重点是填颜色哦= =
不好意思,TUT,请你在回答啊一次吧TUT,直接把代码写上吧TUT
悬赏依旧50TUT
/>1,以下是主程序代码
Private Sub CommandButton1_Click()
On Error Resume Next
For i = 1 To Range("a65536").End(xlUp).Row
Range("a" & i).Interior.ColorIndex = Asc(Left(GetHzjp(Range("a" & i)), 1)) - 63
Next
End Sub
2,以下是自定义函数的代码,写在模块里面.
Function GetHzjp(strHz As String) As String
Dim num As Long
Dim i As Long
GetHzjp = ""
For i = 1 To Len(strHz)
num = Asc(Mid(LCase(strHz), i, 1))
If num > 0 And num <= 127 Then GetHzjp = GetHzjp + Chr(num)
If num >= -23647 And num <= -23554 Then GetHzjp = GetHzjp + Chr(num + 23680)
If num >= &HB0A1 And num <= &HB0C4 Then GetHzjp = GetHzjp + "a"
If num >= &HB0C5 And num <= &HB2C0 Then GetHzjp = GetHzjp + "b"
If num >= &HB2C1 And num <= &HB4ED Then GetHzjp = GetHzjp + "c"
If num >= &HB4EE And num <= &HB6E9 Then GetHzjp = GetHzjp + "d"
If num >= &HB6EA And num <= &HB7A1 Then GetHzjp = GetHzjp + "e"
If num >= &HB7A2 And num <= &HB8C0 Then GetHzjp = GetHzjp + "f"
If num >= &HB8C1 And num <= &HB9FD Then GetHzjp = GetHzjp + "g"
If num >= &HB9FE And num <= &HBBF6 Then GetHzjp = GetHzjp + "h"
If num >= &HBBF7 And num <= &HBFA5 Then GetHzjp = GetHzjp + "j"
If num >= &HBFA6 And num <= &HC0AB Then GetHzjp = GetHzjp + "k"
If num >= &HC0AC And num <= &HC2E7 Then GetHzjp = GetHzjp + "l"
If num >= &HC2E8 And num <= &HC4C2 Then GetHzjp = GetHzjp + "m"
If num >= &HC4C3 And num <= &HC5B5 Then GetHzjp = GetHzjp + "n"
If num >= &HC5B6 And num <= &HC5BD Then GetHzjp = GetHzjp + "o"
If num >= &HC5BE And num <= &HC6D9 Then GetHzjp = GetHzjp + "p"
If num >= &HC6DA And num <= &HC8BA Then GetHzjp = GetHzjp + "q"
If num >= &HC8BB And num <= &HC8F5 Then GetHzjp = GetHzjp + "r"
If num >= &HC8F6 And num <= &HCBF9 Then GetHzjp = GetHzjp + "s"
If num >= &HCBFA And num <= &HCDD9 Then GetHzjp = GetHzjp + "t"
If num >= &HCDDA And num <= &HCEF3 Then GetHzjp = GetHzjp + "w"
If num >= &HCEF4 And num <= &HD188 Then GetHzjp = GetHzjp + "x"
If num >= &HD1B9 And num <= &HD4D0 Then GetHzjp = GetHzjp + "y"
If num >= &HD4D1 And num <= &HF7F9 Then GetHzjp = GetHzjp + "z"
Next
GetHzjp = UCase(GetHzjp)
End Function
Private Sub CommandButton1_Click()
On Error Resume Next
For i = 1 To Range("a65536").End(xlUp).Row
Range("a" & i).Interior.ColorIndex = Asc(Left(GetHzjp(Range("a" & i)), 1)) - 63
Next
End Sub
2,以下是自定义函数的代码,写在模块里面.
Function GetHzjp(strHz As String) As String
Dim num As Long
Dim i As Long
GetHzjp = ""
For i = 1 To Len(strHz)
num = Asc(Mid(LCase(strHz), i, 1))
If num > 0 And num <= 127 Then GetHzjp = GetHzjp + Chr(num)
If num >= -23647 And num <= -23554 Then GetHzjp = GetHzjp + Chr(num + 23680)
If num >= &HB0A1 And num <= &HB0C4 Then GetHzjp = GetHzjp + "a"
If num >= &HB0C5 And num <= &HB2C0 Then GetHzjp = GetHzjp + "b"
If num >= &HB2C1 And num <= &HB4ED Then GetHzjp = GetHzjp + "c"
If num >= &HB4EE And num <= &HB6E9 Then GetHzjp = GetHzjp + "d"
If num >= &HB6EA And num <= &HB7A1 Then GetHzjp = GetHzjp + "e"
If num >= &HB7A2 And num <= &HB8C0 Then GetHzjp = GetHzjp + "f"
If num >= &HB8C1 And num <= &HB9FD Then GetHzjp = GetHzjp + "g"
If num >= &HB9FE And num <= &HBBF6 Then GetHzjp = GetHzjp + "h"
If num >= &HBBF7 And num <= &HBFA5 Then GetHzjp = GetHzjp + "j"
If num >= &HBFA6 And num <= &HC0AB Then GetHzjp = GetHzjp + "k"
If num >= &HC0AC And num <= &HC2E7 Then GetHzjp = GetHzjp + "l"
If num >= &HC2E8 And num <= &HC4C2 Then GetHzjp = GetHzjp + "m"
If num >= &HC4C3 And num <= &HC5B5 Then GetHzjp = GetHzjp + "n"
If num >= &HC5B6 And num <= &HC5BD Then GetHzjp = GetHzjp + "o"
If num >= &HC5BE And num <= &HC6D9 Then GetHzjp = GetHzjp + "p"
If num >= &HC6DA And num <= &HC8BA Then GetHzjp = GetHzjp + "q"
If num >= &HC8BB And num <= &HC8F5 Then GetHzjp = GetHzjp + "r"
If num >= &HC8F6 And num <= &HCBF9 Then GetHzjp = GetHzjp + "s"
If num >= &HCBFA And num <= &HCDD9 Then GetHzjp = GetHzjp + "t"
If num >= &HCDDA And num <= &HCEF3 Then GetHzjp = GetHzjp + "w"
If num >= &HCEF4 And num <= &HD188 Then GetHzjp = GetHzjp + "x"
If num >= &HD1B9 And num <= &HD4D0 Then GetHzjp = GetHzjp + "y"
If num >= &HD4D1 And num <= &HF7F9 Then GetHzjp = GetHzjp + "z"
Next
GetHzjp = UCase(GetHzjp)
End Function