Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
change font of certain cells in row(s) when ColorIndex of Column S changes
I am an intermediate user of Excel and VBA. I have a problem where
exported worksheets are transferred and then printed out. The rows are color coded as to what to do if a row is a certain color. However we don't use/have a color printer, it does give different shades of grey, but is not all that discernable on a printout. I took an original DAVID MCRITCHIE routine and modified it. (Thank you very much Mr. McRitchie J) Sub ChangeFontPerColorindexOfColS() 'Commented out UPPER CASE sections are from Original which precedes areas I needed to subsitute. 'Modified David McRitchie Sub Originally {Sub DeleteRowsRedIncolA() } 'DAVID MCRITCHIE 2002-01-17 ' HTTP://WWW.MVPS.ORG/DMCRITCHIE/EXCEL/COLORS.HTM 'WILL NOT FIND COLOR DUE TO CONDITIONAL FORMATTING 'Again, Thank you very much Mr. McRitchie J Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 'pre XL97 xlManual Dim rng As Range, ix As Long ' SET RNG = INTERSECT(RANGE("A:A"), ACTIVESHEET.USEDRANGE) Set rng = Intersect(Range("S:S"), ActiveSheet.UsedRange) For ix = rng.Count To 1 Step -1 If rng.Item(ix).Interior.ColorIndex = 3 Then rng.Item(ix).Font.Bold = True ' rng.Item(ix).EntireRow.Delete With rng.Item(ix).Font .Name = "Arial" .Size = 12 End With End If Next Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub The above modified David McRitchie Sub originally {Sub Delete RowsRedInColA() } will change the font and size of all rows at the intersect(Column S). My question is, how can I modify this to change font of certain cells in the row(s) when ColorIndex of Column S changes. IE: If the colorindex of S is 3 (red), I want to change the font to bold and size to 12, for columns A thru R,in that row. If the colorindex of S is 7(purple) I want to change the font to bold and size 12, for columns A & B in that row. If the colorindex of S is 8(ltblue) I want to change the font to bold for column A in that row. I have tried so many different combinations (offsets, ranges, etc), there are too many to list here. I am embarrassed to say also I have been working on this for months. I tried changing it to select cells per the color index, and it appears to stall after changing/selecting only the first row. It seems I am not declaring something or I need an array (which I don't understand all that well), or.... . I dunno. I am using this as a tool to learn stepping thru data and IF statements. I also tried Select Case scenarios to no avail. If I can get a clear cut reason as to why the routine stops after the first iteration, it may give me some good insight as to logical syntax in IF and Select Case statements. I hope Mr. McRitchie is ok with me using his stuff. Problems are just opportunities for achievements, to someone. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cells won't change font color or show hi-lighted cells in document | Excel Discussion (Misc queries) | |||
If Font.ColorIndex = 5 then . . . | Excel Programming | |||
VBA syntax for Font & Interior ColorIndex | Excel Discussion (Misc queries) | |||
unable to set the colorindex property of the font class | Excel Programming | |||
Font and Fill ColorIndex | Excel Programming |