EXCEL如何用VBA设定SHEET1输入文字，在SHEET2数据库里自动查找并标颜色 excel 如何利用VBA 把sheet1中内容在sheet...
EXCEL\u5982\u4f55\u7528VBA\u8bbe\u5b9aSHEET1\u4e2d\u7684A1\u8f93\u5165\u4e00\u4e2a\u59d3\u540d\u540e\uff0cB1\u63d0\u53d6\u59d3\u540d\u540e\u53bbSHEET2\u6570\u636e\u5e93\u91cc\u81ea\u52a8\u67e5\u627e\u76f8\u540c\u7684\u540d\u5b57\u6570\u636e\u662f\u8981\u67e5\u627e\u5f15\u7528\u7b26\u5408\u6761\u4ef6\u7684\u6570\u636e\u5bf9\u5427\uff1f\u8c8c\u4f3c\u516c\u5f0f\u5c31\u53ef\u4ee5\u89e3\u51b3\u3002
\u5728\u8868\u4e00\u7684B1\u8f93\u5165\u516c\u5f0f\uff1a
=vlookup($a1,sheet2!$a:$c,column(b:b),0)
\u516c\u5f0f\u5f80\u53f3\u62c9\uff0c\u5f80\u4e0b\u62c9
\u5982\u679c\u67e5\u627e\u7684\u6570\u636e\u4e0d\u6b62\u4e09\u5217\uff0c\u90a3\u5c31\u5c06$c\u6539\u6210$d\u6216\u8005\u66f4\u591a\uff0c\u5176\u4ed6\u7684\u4e0d\u4fbf
\u672c\u6765\u60f3\u5e2e\u5fd9\uff0c\u4f46\u662f\u88ab\u641e\u6655\u4e86\uff0c\u5b9e\u5728\u4e0d\u6e05\u695a\u8868\u793a\u7684\u610f\u601d\uff0c\u4e0b\u9762\u7684\u80fd\u8bf4\u660e\u767d\u5417\uff1f 1\u3001\u201c\u8fc7\u6765\u7684H1\u5217\u7684\u5355\u5143\u683c\u201d\u4ec0\u4e48\u610f\u601d\uff0c\u4ece\u90a3\u6765\u7684\uff0c\u600e\u4e48\u6765\u7684\uff1f 2\u3001\u53ef\u4e0d\u53ef\u4ee5\u4e0d\u8981\u628a\u5355\u72ec\u7684\u5355\u5143\u683c\u548c\u5217\u653e\u5728\u4e00\u8d77\u8bf4\u554a\uff0c\u5230\u5e95\u662f\u4e00\u5217\u8fd8\u662f\u4e00\u4e2a\u5355\u5143\u683c\uff1f\uff1f\u771f\u662f\u6655\u554a\uff01\uff01 \u201cF1\u5217\u201d\u201cH1\u5217\u201d\u201c\u6211\u67e5\u627e\u7684\u5217\u7684H1\u6570\u636e\u201d\u201cA1\u548cF1\u5217\u201d \u4ee5\u4e0a\u8bf4\u6cd5\u592a\u96be\u7406\u89e3~
Private Sub Worksheet_Change(ByVal Target As Range)Dim i As Long
Dim s As String
If Target.Column = 2 And Target.Count = 1 Then' 指定B列任意一个单元格输入
s = Target.Value
With Sheet2
For i = 1 To .Range("B65536").End(xlUp).Row'从表2第1行循环到B列最后一个非空行号
If .Cells(i, 2) = s Then
.Rows(i).Interior.ColorIndex = 3‘找到行红底
Else
.Rows(i).Interior.ColorIndex = 0'不符合的无底色
End If
Next
End With
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
s = Cells(Target.Row, Target.Column)
For i = 1 To 10000
ss = Sheet2.Cells(i, 1)
If ss = Empty Then Exit For
If ss = s Then
Sheet2.Rows(i).Font.Color = vbRed
Exit For
End If
Next
End If
End Sub
绛旓細鏂规硶/姝ラ濡備笅锛1銆佸弻鍑绘墦寮妗岄潰鐨EXCEL锛2銆佸崟鍑诲彸涓婅鐨凮FFICE鍥炬爣锛屾壘鍒癊XCEL閫夐」锛屽崟鍑绘墦寮锛3銆佸嬀閫夊湪鍔熻兘鍖烘樉绀衡滃紑鍙戝伐鍏封濋夐」鍗★紝鍗曞嚮纭畾鎸夐挳瀹屾垚淇敼銆4銆佸崟鍑昏彍鍗曟爮鐨勫紑鍙戝伐鍏凤紝鐒跺悗鎵惧埌Visual Basic鍗曞嚮鎵撳紑渚垮彲浠ヨ繘琛岀浉鍏崇殑VBA缂栫▼銆
绛旓細濡備綍浣跨敤VBA鎿嶆帶Excel鐣岄潰锛熸湰鏂囪瑙ｆ搷鎺у伐浣滆〃涓竴浜涚晫闈㈠厓绱犵殑VBA浠ｇ爜銆傚伐浣滆〃涓殑琛屽垪 闅愯棌鍜屽彇娑堥殣钘忚鏍囬 涓嶈兘澶熷垎鍒崟鐙殣钘忚鏍囬鍜屽垪鏍囬锛屽彧鑳藉悓鏃堕殣钘忔垨鑰呮樉绀鸿繖涓や釜鏍囬銆傜ず渚嬩唬鐮侊細鈥橀殣钘忓拰鍙栨秷闅愯棌娲诲姩绐楀彛涓椿鍔ㄥ伐浣滆〃鐨勮鏍囬鍜屽垪鏍囬鈥橀殣钘忔爣棰楢ctiveWindow.DisplayHeadings= False鈥樺彇娑堥殣钘忔爣棰...
绛旓細Application.ScreenUpdating = False Dim sh As Workbook Filename = Application.GetOpenFilename(FileFilter:="Access 鏁版嵁搴撴枃浠 (*.mdb),*.mdb", Title:="璇烽夋嫨鏂囦欢")If Filename <> False Then If Dir(Filename) <> ThisWorkbook.Name Then Set sh = Workbooks.Open(Filename)'鍙栫敓鎴愭柊琛...
绛旓細Public Sub HBsh() Dim Sh As Worksheet, i As Long i = 1 For Each Sh In Worksheets If Sh.Name <> "鎬荤殑" Then Sheets("鎬荤殑").Cells(1, i).Resize(Sh.Range("A1").CurrentRegion.Rows.Count, Sh.Range("A1").CurrentRegion.Columns.Count).Value = Sh.Range(...
绛旓細1銆佸湪Excel涓鏂板缓涓涓枃妗ｏ紝骞跺皢瀹冨懡鍚嶄负鈥滄彁閱.xls鈥濄2銆佹墽琛岃彍鍗曟爮涓殑鈥滃伐鍏封濃啋鈥滃畯鈥濃啋鈥淰isual Basic缂栬緫鍣ㄢ濆懡浠わ紝杩涘叆VBA缂栬緫鐘舵併3銆佸湪鈥滃伐绋嬭祫婧愮鐞嗗櫒鈥濈獥鍙ｄ腑锛岄変腑鈥淰BAProject锛堟彁閱.xls锛夆濋夐」锛岀劧鍚庢墽琛岃彍鍗曟爮涓殑鈥滄彃鍏モ濃啋鈥滄ā鍧椻濆懡浠わ紝鎻掑叆涓涓悕涓衡滄ā鍧1鈥濈殑鏂版ā鍧椼4銆...
绛旓細Excel鎬庢牱蹇熸壒閲忓皢鐩稿悓琛ㄥ紡澶氭枃浠舵暟鎹慨鏀
绛旓細excel2007鍒涘缓骞浣跨敤VBA浠ｇ爜鐨勬搷浣滄暀绋嬬偣鎸夎鍥惧伐鍏锋爮visualbasic銆傜晫闈笂浼氬嚭鐜皏isualbasic宸ュ叿鏉★紝鎷栧姩宸ュ叿鏉″乏渚у埌涓婃柟鏉惧紑榧犳爣锛岀劧鍚庣偣visualbasic缂栬緫鍣ㄣ傝繘鍏isualbasic鐣岄潰鍚庣偣鎸夎彍鍗曟爮鎻掑叆妯″潡寮瑰嚭妯″潡鐣岄潰銆傝緭鍏ヤ竴娈典唬鐮佸鍥剧孩妗嗘墍绀恒傜偣鎸夎彍鍗曟爮杩愯杩愯瀛愯繃绋/鐢ㄦ埛绐椾綋F5銆傚湪寮瑰嚭鐨勫璇濇涓緭鍏ヤ竴涓...
绛旓細渚嬪浣犺鍚慍3鍐欐暟鎹,鐢ㄥ紩鐢=, 灏辫浜,濡俁ange("C3")=123 鍙互浣跨敤涓嬮潰鍒椾妇鐨勪换涓鏂瑰紡瀵瑰綋鍓嶅伐浣滆〃涓殑鍗曞厓鏍(C3)杩涜寮曠敤銆(1) Range("C3")(2) [C3](3) Cells(3, 3)(4) Cells(3, "C")(5) Range("C4").Offset(-1)Range("D3").Offset(, -1)Range("A1").Offset(2, 2)(...
绛旓細浠ｇ爜鍙婃敞閲婂涓嬶細Sub m()For Each sh In Sheets '閬嶅巻鍏ㄩ儴宸ヤ綔琛 If sh.Name <> ActiveSheet.Name Then '濡傛灉宸ヤ綔琛ㄤ笉涓哄綋鍓嶅伐浣滆〃 sh.Cells(1, 1) = ActiveSheet.Name '璁╁伐浣滆〃鐨勭涓涓崟鍏冩牸濉厖褰撳墠宸ヤ綔琛ㄥ悕 End If Next End Sub ...
绛旓細1銆佺偣鍑Excel鐣岄潰宸︿笅瑙掔殑褰曞埗瀹忔寜閽綍鍒跺畯銆2銆佸湪銆愬畯鍚嶃戝杈撳叆涓涓湁鎰忎箟鐨勫悕绉帮紝甯姪浠ュ悗璇嗗埆璇ュ畯鐨勫姛鑳姐傛湰渚嬫寜鐓ч粯璁ゅ悕绉板懡鍚嶃傞夋嫨灏嗗畯褰曞埗鍦ㄣ愪釜浜哄畯宸ヤ綔绨裤戝苟鐐瑰嚮銆愮‘瀹氥戞寜閽3銆侀┈涓婄偣鍑籈xcel鐣岄潰宸︿笅瑙掔殑鍋滄褰曞埗瀹忔寜閽仠姝㈠畯鐨勫綍鍒躲4銆佹寜Alt+F11鎵撳紑VBE鐣岄潰锛屽涓嬪浘銆5銆佸湪浠ｇ爜閮ㄥ垎...
