My Crappy Code Needs Fixing (1 Viewer)

DanG

Registered User.
Local time
Today, 07:36
Joined
Nov 4, 2004
Messages
477
Hi,

The crappy code I made below is designed to condisionaly format on 2 columns, "Group Nmber" and "50/50 Category". Under "Group Number" I want all rows highlighted grey that are "or" contain "69472". And "50/50" I would like the entire row to be bold and italic if the cell contains either "Default" or "At Risk".

The code below works, but does not highlight the row grey if the "50/50" column contains a "default" or "at risk", it will only bold and italicise that row.

Help :)

Code:
' Make 50/50 bold and highlight GA# 69472 -----------------

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim MyCol As String
Dim MySDA As String
 
Rows("1:1").Select
    Selection.Find(What:="*50/50*", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select

MyCol = "$" & Split(ActiveCell.Address, "$")(1) & "1"

Rows("1:1").Select
    Selection.Find(What:="Group Numbers", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select

MySDA = "$" & Split(ActiveCell.Address, "$")(1) & "1"
 
  Range("A1:CA6000").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=IF(OR(" & MyCol & "=""At Risk""," & MyCol & "=""Default: Please Reassign""),1,0)"
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = True
    End With
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTIF(" & MySDA & "," & Chr(34) & Chr(42) & "69472" & Chr(42) & Chr(34) & ")>0"
        Selection.FormatConditions(2).Interior.ColorIndex = 15
        
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTIF(" & MySDA & "," & "69472" & ")>0"
       Selection.FormatConditions(3).Interior.ColorIndex = 15
       
' End Make 50/50 bold ----------------------------------------------
 

Roku

MBCS CITP
Local time
Today, 14:36
Joined
Sep 26, 2013
Messages
112
Is there any reason you are using code to do this? It looks to me as if you could achieve what you want using conditional formatting.
If you really do want to use code, try the format rule using the Ribbon, then use the result to determine the format rule you need.
If you still need help, please explain why your approach is required.
 

DanG

Registered User.
Local time
Today, 07:36
Joined
Nov 4, 2004
Messages
477
Thank you for your reply!

I am using Access 2003, no ribbon.

Code is needed because this is part of an add-in that multiple users have installed on their computer. Then they download the client list, clean it up (which this is a part of that process) and then they send it to the requester to review.

Is this not possible to to in code?
 

namliam

The Mailman - AWF VIP
Local time
Today, 15:36
Joined
Aug 11, 2003
Messages
11,696
Formatting conditions stop at the first they meet the requirements for, appearently all your rows meet the first requirement therefor never get to the second... This may be an issue...

Also your formatting rules are convoluted
"=IF(OR(" & MyCol & "=""At Risk""," & MyCol & "=""Default: Please Reassign""),1,0)"
"=OR(" & MyCol & "=""At Risk""," & MyCol & "=""Default: Please Reassign"")" will work just as well....

What your code actually does is hardcode B1 or C1 depending on where it finds the values you are searching for

Instead you need to use a softcoded way of finding your cells per row to find out how to format the current row/column combination. My excel is too rusty and I would have to dig deep but IIRC it has something to do with Offset and/or Address, currentcell/activecell... but it has a lot of dust on it.

I hope this helps
 

Bilbo_Baggins_Esq

Registered User.
Local time
Today, 09:36
Joined
Jul 5, 2007
Messages
586
wait a minute, your using Access 2003 but we're talking about Excel?
Are you using the Excel 2003 object model?

I know there is an option in 2k7 and 2010 to continue the condition evaluation past the first yes, but I can't remember for 2k3.

I think some clarity would be helpful here on where this code is being initiated (what app)
 

DanG

Registered User.
Local time
Today, 07:36
Joined
Nov 4, 2004
Messages
477
Sorry for the confusion, never have been good at multitasking, I am working in Excel 2003 :)

But, we will be moving to Office 2010 next year, so maybe there is hope...in the future.

I don't think the limit is my problme, correct me if I am wrong. I do have 3 conditions.
1.) 50/50 column = if any cell is "default" or "at risk" make entire row bold and italic.
2.) Group Number column = if any cell is "69472" make entire row grey
3.) Group Number column = if any cell is "*69472*" make entire row grey.

The reason that item 2 & 3 are not combined like item one is there seems to be a limitation in excel when the * is used.

I hope this clears it up.
 

namliam

The Mailman - AWF VIP
Local time
Today, 15:36
Joined
Aug 11, 2003
Messages
11,696
What your code actually does is hardcode B1 or C1 depending on where it finds the values you are searching for

Instead you need to use a softcoded way of finding your cells per row to find out how to format the current row/column combination. My excel is too rusty and I would have to dig deep but IIRC it has something to do with Offset and/or Address, currentcell/activecell... but it has a lot of dust on it.
Did you even see this part or even see my post?

I was thinking perhaps the hardcoded thing may work if you only apply it to row 1 and copy/paste format to the rest... but like i said my excel coding is a little dusty.
 

Roku

MBCS CITP
Local time
Today, 14:36
Joined
Sep 26, 2013
Messages
112
As Bilbo_Baggins_Esq identified, the StopIfTrue condition was introduced in Excel 2007, which is what you need for your version to work.

If I interpret your requirements correctly, you have three distinct conditions:
1. one column contains a number (69472) or a string ("*69472") of interest (format background grey)
2. one column contains two strings ("At Risk", "Default ...") of interest (format font bold/italic)
3. both of above together (format b/g grey and font bold/italic)

If you assign a numeric value to conditions 1 (value=1) and 2 (value=2) and add the result, you get values 0, 1, 2 or 3, depending on the particular combination for a row. Thus, apply formats according to those resultant values and you get what you want.

The following code assigns three format rules per above.
Code:
Private Sub setConditionalFormats()
Dim rg As Range
Dim i As Integer
Dim strCol(1) As String
Rem find columns of interest
For i = 0 To 1
  Set rg = ActiveSheet.Rows(1).Find(What:=Choose(i + 1, "50/50", "Group"), After:=ActiveSheet.[a1], LookIn:=xlValues, LookAt:=xlPart)
  If rg Is Nothing Then
    MsgBox Choose(i + 1, "50/50", "Group") & " column not found", vbCritical, "Failed Search"
    Exit Sub
  Else
    Rem save column number for search item
    strCol(i) = rg.Address(RowAbsolute:=False, ColumnAbsolute:=True)
  End If
Next
Rem clear existing FormatConditions
ActiveSheet.Cells.FormatConditions.Delete
Rem apply FormatConditions
Dim fc As FormatCondition
Dim strRule As String
For i = 3 To 1 Step -1
  strRule = "=(IF(OR($B1=69472,$B1=""*69472""),1,0)+IF(OR($D1=""At Risk"",$D1=""Default: Please Reassign""),2,0))=" & CStr(i)
  Set fc = ActiveSheet.Cells.FormatConditions.Add(Type:=xlExpression, Formula1:=strRule)
  fc.Font.Bold = (i And 2) = 2
  fc.Font.Italic = fc.Font.Bold
  If (i And 1) = 1 Then fc.Interior.ColorIndex = 15
  fc.StopIfTrue = True
Next
End Sub
I have tested this using Excel 2007, so it may not work exactly as-is in Excel 2003.

For clarity, the format conditions are resolved as:
Code:
=(IF(OR($B1=69472,$B1="*69472"),1,0)+IF(OR($D1="At Risk",$D1="Default: Please Reassign"),2,0))=n
where n is 3, 2 or 1 and the columns of interest are B (condition 1) and D (condition 2).
 

DanG

Registered User.
Local time
Today, 07:36
Joined
Nov 4, 2004
Messages
477
Did you even see this part or even see my post?

namliam - You know, sometimes things come accross a bit harsh in writing, I'll give you the benifit of the doubt on this one :)
Your input, to me anyways, was a bit abstract for my skills and you did not provide any sample code or pseudo code to better illustrate your concept.

Roku -

Thank you very much for your solution, on paper it looks good and is something I can understand. I'll give it a try, it may take me a while but it makes sense.

Thank you
 

namliam

The Mailman - AWF VIP
Local time
Today, 15:36
Joined
Aug 11, 2003
Messages
11,696
If you want to be making formatting rules like this, like I said, you cannot always hardcode them...

Your code will hardcode your cells, this will be an issue in older versions of office unless you are using 2007 or newer IIRC....
To softcode them you need something like: Indirect("B" & Row())
to reference the current row's column B, your code will always reference B1

For example see here: http://ask.metafilter.com/207012/Ho...to-a-Conditional-Formatting-Forumual-in-Excel
 

DanG

Registered User.
Local time
Today, 07:36
Joined
Nov 4, 2004
Messages
477
Thanks namliam, that makes sense!
 

Users who are viewing this thread

Top Bottom