|
Conditional format via VBA
Place your words in a helper column, in my example it is column A in a helper
worksheet named "pattern", set the backgrond color of cells to the required color for the word in them, and run this macro: Sub szinez() Dim colD As Range noofrows = Columns("D:D").End(xlDown).Row Set colD = Range("D2:D" & noofrows) For Each dcell In colD hit = WorksheetFunction.Match(dcell.Value, Worksheets("pattern").Columns("A:A"), 0) colorno = Worksheets("pattern").Range("A" & hit).Interior.ColorIndex dcell.EntireRow.Interior.ColorIndex = colorno Next dcell End Sub Regards, Stefi €˛bijan€¯ ezt Ć*rta: Hi all, could someone please help me to write a vb code with SELECT CASE or IF statment? I have a sheet with about 5000 rows (A2:K5000)and use some words like XTT,YBB,NZZ,USS,...(about 10 words) in column D . what I need to do is each row that contain these word have spetial color in list for example: .....................D.............. .....................NZZ.......... red .....................XTT......... blue Thanks in advance |
Conditional format via VBA
Thank you Stefi
apologize me for responsing late, the code worked and colored my list, but some thing is strange ,after run the code some of the colors created in the list are different from my defination, for example light orange and light gray both appear light yellow , indeed,if it possible I want to know can I define some properties such as forcolor or fontsize with my words in pattern sheet at same time. many regards Bijan "Stefi" wrote: hit = WorksheetFunction.Match(dcell.Value, _ Worksheets("pattern").Columns("A:A"), 0) This must be one line in VBA, and as I see, this message window broke it into two parts. I Inserted a line break to avoid it. Have you named the helper sheet "pattern"? Sub szinez() Dim colD As Range noofrows = Columns("D:D").End(xlDown).Row Set colD = Range("D2:D" & noofrows) For Each dcell In colD hit = WorksheetFunction.Match(dcell.Value, _ Worksheets("pattern").Columns("A:A"), 0) colorno = Worksheets("pattern").Range("A" & hit).Interior.ColorIndex ' dcell.EntireRow.Interior.ColorIndex = colorno Range("A"&dcell.Row&":K"&dcell.Row).Interior.Color Index = colorno Next dcell End Sub Regards, Stefi €˛bijan€¯ ezt Ć*rta: Stefi Genius Thanks, I have done what you said, But I am reciveing an error(run-time error '1004') in line hit=.............. with just one row colored in top of my list,and is it posible to color a range(A:K) instead of entire row? A.....................D........................... .......K 1 ......................USS......................... ...... RED 2 ......................NZZ......................... ....... BLUE Bijan Stefi" wrote: Place your words in a helper column, in my example it is column A in a helper worksheet named "pattern", set the backgrond color of cells to the required color for the word in them, and run this macro: Sub szinez() Dim colD As Range noofrows = Columns("D:D").End(xlDown).Row Set colD = Range("D2:D" & noofrows) For Each dcell In colD hit = WorksheetFunction.Match(dcell.Value, Worksheets("pattern").Columns("A:A"), 0) colorno = Worksheets("pattern").Range("A" & hit).Interior.ColorIndex dcell.EntireRow.Interior.ColorIndex = colorno Next dcell End Sub Regards, Stefi €˛bijan€¯ ezt Ć*rta: Hi all, could someone please help me to write a vb code with SELECT CASE or IF statment? I have a sheet with about 5000 rows (A2:K5000)and use some words like XTT,YBB,NZZ,USS,...(about 10 words) in column D . what I need to do is each row that contain these word have spetial color in list for example: .....................D.............. .....................NZZ.......... red .....................XTT......... blue Thanks in advance |
| All times are GMT +1. The time now is 12:07 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com