Conditional Formatting and re-evaluate

[JustinDevine]

All,

I have read JRD's post on COnditional formatting. It is very useful. However the VBA doesn't recalculate a cell's formatting until it is changed. Therefore if you require a change in any of the values that cause a change, the cells that had been formatted based on your previous values stay with their previous formatting. I need a way to check all cells at the opening of a sheet (at a minimum) and recheck all the cells for the proper value using the conditional formatting code. thanks for all your help on Yahoo JRD. BTW JRD suggested this code

Private Sub Workbook_Open()
Dim Cel As Range For Each Cel In Sheets("Sheet1").UsedRange Cel.Value = Cel.Value NextEnd Sub

or something similar. Although I think it is malformed due to using IM as the medium. Can anyone help?

thanks

-Justin

[JustinDevine]

Sorry "DRJ"

[lenze]

What are your conditional formats and how are they applied? You can loop through the cells and use Select Case. Can you attach a sample file?

lenze

[JustinDevine]

I am just using the code from this KB post. The issue is that I want it to recheck every cell at some point so that it can "unformat" criteria that I have removed

http://vbaexpress.com/kb/getarticle.php?kb_id=90

[lenze]

If the code works for a change event, can't you place it in a regular module and run it on demand or on an Open_Event? Something like

Dim cl as Range
For each cl in ActiveSheet.UsedRange
Select Case cl
Case "whateever"
      'Formatting code
Case "otherwhatever"
      'Formatting code
'etc.
Case Else
End select
Next cl

lenze

[JustinDevine]

Unfortunately I am a complete noob never even seen VBA before today. I have some limited programming knowledge so I am not completely in the dark, but maybe you can help. I tried to just copy the example into the WorkBook_Open in the ThisWorksheet module with no success.

[lucas]

Hi DustinJevine.....sorry coudn't resist

joking aside, if you "remove criteria" by clearing contents, etc. this will automatically go back to normal. from the code:
[vba]
Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
[/vba]also try typing in a number 100 or larger. Unless I'm missing something.

[lucas]

This is Worksheet_Change code so it needs to go in the code for the specific sheet you wish to use it on. right click on the sheet tab in excel without opening the vbe and then click on view code.....put the code in the module that opens..not in Thisworkbook

[JustinDevine]

Yes this is true they will go back to normal, but only after you somehow edit the cell. I need a way to go through the sheet and check all the cells? Without having to select or re-enter values into every one.

[lucas]

Are you using formula's or something because I'm not following.....if you change anything.....anything on the sheet then this routine runs. so if you have changed something then the program has made the corrections. If you just feel uncomfortable you could record a simple macro that makes a change and add a button to run it but unless I misunderstand you it seems unnecessary.....can you clarify

[JustinDevine]

Well, what I am finding is this.

Lets say I change my criteria from

Case "Tom", "Paul", "Joe"
Cell.Interior.ColorIndex = 3
Cell.Font.Bold = True

TO

Case "Tom", "Paul", "John"
Cell.Interior.ColorIndex = 3
Cell.Font.Bold = True

The "Joe" cells STAY RED. (ColorIndex = 3)

My issue is i need them to go back to white when I change my Case Statements. A bit clearer now? Thanks!

[JustinDevine]

Here is an Example I was playing with. As you can see "Joe" is no longer in a Case statement but the Joe cells stay red, the ones that are not red are ones in which I have re-entered the value.

[lenze]

Put this code in a regular module and run it on your sample sheet.

Sub ReFormat()
    Dim cl As Range
    For Each cl In ActiveSheet.UsedRange
    Select Case cl
    Case Is = "Tom", "Paul"
        cl.Interior.ColorIndex = 5
        cl.Font.Bold = True
    Case Else
        cl.Interior.ColorIndex = xlNone
        cl.Font.Bold = False
    End Select
    Next cl
End Sub

Change as needed.

lenze

[JustinDevine]

Thanks I believe that got it. I copied the same into workbook_open and achieved what I wanted. Unfortunately this means I have to maintain two sets of code, but its not hard, considering I can just copy/paste the Case Statements. Thanks.

[lenze]

You do not have to change your parameters in the code. You can refer to to cells. Example

Case Is = Range("$A$1").Value, Range("$A$2").Value, Range("$A$3").Value

You might also consider using an array. See help file for syntex.

lenze

BTW: You do not need the code in 2 places. Just put it in a regular module. You can call it from the WorkBook _Open Event

Call Reformat

[JustinDevine]

Private Sub Workbook_Open()
Call Reformat
End Sub


On calling Reformat from Workbook_Open

---------------------------
Microsoft Visual Basic
---------------------------
Compile error:

Expected variable or procedure, not module
---------------------------

[lucas]

Justin, could you post your workbook so we could take a look...remove any personal or critical info and personal stuff in the workbook properties.

[lucas]

Try this, I just put lenze's code in the workbook open so you'll have to adjust it to suit your need.

[lucas]

The reason you had a problem is that you called the macro Reformat
from workbook open but didn't put the code in a module. Can do it that way too

[JustinDevine]

DRJ was kind enough to give me the following code to place in worksheet_Change. It works wonderfully. all I wanted was to acheive the same function but not only during a cell change but also on workbook_open. Oh and prefereably on every sheet not just the sheet the code is attached to. Sorry I am new to this and my previous posts have been poorly described. I am getting a book on VBA in the mail though so I won't suck forever :-)

[VBA]Option Compare Text 'A=a, B=b, ... Z=z
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim Rng1 As Range

On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)
Else
Set Rng1 = Union(Range(Target.Address), Rng1)
End If
For Each Cell In Rng1
Select Case Cell.Value
Case vbNullString
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
Case "Tom", "Paul", "John"
Cell.Interior.ColorIndex = 3
Cell.Font.Bold = True
Case "Smith", "Jones"
Cell.Interior.ColorIndex = 4
Cell.Font.Bold = True
Case 1, 3, 7, 9
Cell.Interior.ColorIndex = 5
Cell.Font.Bold = True
Case 10 To 25
Cell.Interior.ColorIndex = 6
Cell.Font.Bold = True
Case 26 To 99
Cell.Interior.ColorIndex = 7
Cell.Font.Bold = True
Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
End Select
Next

End Sub

[/VBA]