Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i ignore cells with font color?
I just cannot get my code to do what i want. I hope someone can help me. I am trying to get it to loop through all the values in the range and color the font of certain low values. The way i think it would work best is if it could just ignore the values that it already has colored because as it stands, i can only get it to see one low value per range. my question is: how can i get it to ignore font without the default black? for example blue or red font. here is a snippet of code for that portion of the program.. MUCH thanks for your assistance, because right now it's in an endless loop. AHHHH! grubb = Cells(J + K, "V").value tlow = Cells(J + K, "T").value thigh = Cells(J + K, "U").value While tlow grubb 'code to ignore cells with color font color other than default black since i would like it to color all the tlow values greater than the grubb value. If ActiveCell.Font.ColorIndex < xlAutomatic And ActiveCell.Font.ColorIndex < 1 Then Set rng = Range("D" & J + K, "N" & J + K) lowval = Application.WorksheetFunction.Min(rng) result = Application.Match(lowval, rng, 0) If Not IsError(result) Then rng(1, result).Font.Color = RGB(0, 10, 200) minValue = Application.WorksheetFunction.Min(Range(rng)) 'should return minimum value without the cells with colored font tlow = (Cells(J + K, "R").value - minValue) / (Cells(J + K, "S").value) 'calc new tlow for the "when" condition End If Wend ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i ignore cells with font color?
thank you, i have read that information before... but, i'm still stuck. I just dont know what else to do! any one have ideas???? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i ignore cells with font color?
I don't understand what you are trying to do, but here's something that
should help you get going. Enter the following function in a standard module (watch out for word wrap). It is based on one of Chip's examples. Option Explicit Function ValuesWithColor(InRange As Range, WhatColorIndex As Integer, _ Optional OfText As Boolean = False) As Variant Dim Rng As Range, Rslt As Variant, I As Long ReDim Rslt(1 To InRange.Cells.Count) Application.Volatile True I = 1 For Each Rng In InRange.Cells If OfText = True Then Rslt(I) = IIf(Rng.Font.ColorIndex = WhatColorIndex, Rng.Value, "") Else Rslt(I) = IIf(Rng.Interior.ColorIndex = WhatColorIndex, Rng.Value, "") End If I = I + 1 Next Rng On Error GoTo Huh If Application.Caller.Columns.Count 1 Then ValuesWithColor = Rslt Else ValuesWithColor = Application.WorksheetFunction.Transpose(Rslt) End If Exit Function Huh: ValuesWithColor = Rslt End Function Use the above function in a worksheet formula as =MIN(ValuesWithColor(A1:A4,-4105,TRUE)) or =SUM(ValuesWithColor(A1:A4,-4105,TRUE)) or, in VBA as Sub testIt() MsgBox Application.WorksheetFunction.Min( _ ValuesWithColor(Range("a1:a4"), _ xlColorIndexAutomatic, True)) End Sub Note that -4105 is the numeric value of xlColorIndexAutomatic. -- Regards, Tushar Mehta MS MVP Excel 2000-2003 www.tushar-mehta.com Excel, PowerPoint, and VBA tutorials and add-ins Custom Productivity Solutions leveraging MS Office In article , chick- says... thank you, i have read that information before... but, i'm still stuck. I just dont know what else to do! any one have ideas???? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i ignore cells with font color? | Excel Programming | |||
how do i ignore cells with font color? | Excel Programming | |||
how do i ignore cells with font color? | Excel Programming | |||
how do i ignore cells with font color? | Excel Programming | |||
how do i ignore cells with font color? | Excel Programming |