Anyone Help me Turn these foormulas into VBA ?

mercmannick

mercmannick

Well-known Member
Joined
Jan 21, 2005
Messages
730
hi i have this S/Sheet below..........


That Html maker will not let me post



i can post to anyone who is willin to help





That is full of fomulas , is anyone fancys helping me turn into VBA ,as not only do i need these formulas but also i need a 4th cond format, based on the outcome of these formulas.........


If need be i can email a Copy of the complete sheet.


Im banging head as cant fathom out
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
here is the formulas i am using on part of the sheet on the outcome of this i cond format

=IF(AND(I$2=$G5,I$2=$A5),"AR",IF(AND(I$2=$E5,I$2=$A5),"PR",IF(AND(I$2=$F5,I$2=$G5),"AF",IF(I$2=$A5,"R",IF(I$2=$G5,"A",IF(I$2=$F5,"F",IF(I$2=$E5,"P","")))))))


Merc
 
Upvote 0
all,

i have also posted on VBAX

http://www.vbaexpress.com/forum/showthread.php?t=7706


and Xld has come up with first part of solution on formulas


Code: 
Function CheckDate(ScheduleDate, StartDate, PlanDate, ForecastDate, ActualDate)
    Select Case True
    Case ScheduleDate = ActualDate And _
        ScheduleDate = StartDate: CheckDate = "AR"
    Case ScheduleDate = PlanDate And _
        ScheduleDate = StartDate: CheckDate = "PR"
    Case ScheduleDate = ForecastDate And _
        ScheduleDate = ActualDate: CheckDate = "AF"
    Case ScheduleDate = StartDate: CheckDate = "R"
    Case ScheduleDate = ActualDate: CheckDate = "A"
    Case ScheduleDate = ForecastDate: CheckDate = "F"
    Case ScheduleDate = PlanDate: CheckDate = "P"
    Case Else: CheckDate = ""
    End Select
End Function


now i need to code to cond format based on the range in S/Sheet

anyone give me a boost

Merc
 
Upvote 0
Cond Format to be like

if cell is "A" then green fill and bold black text

if cell is "R" then red fill and bold white text

if cell is "P" orange fill and bold white text

if cell is "F" blue fill and bold white text

if cell is "AR" then green fill and bold black text

if cell is "PR" orange fill and bold white text

if cell is "AF" then green fill and bold black text


And so on


Merc
 
Upvote 0
Code: 
Private Sub Worksheet_Calculate() 
    Dim rng As Range 
     
     ' only check the range named "rngDate"
    For Each rng In Range("rngDate") 
        Select Case rng.Value 
        Case "A", "AF", "AR" 
            With rng 
                .Interior.ColorIndex = 43 
                .Font.Bold = True 
                .Font.ColorIndex = 0 
            End With 
        Case "R" 
            With rng 
                .Interior.ColorIndex = 3 
                .Font.Bold = True 
                .Font.ColorIndex = 2 
            End With 
             
        Case "P", "PR" 
            With rng 
                .Interior.ColorIndex = 45 
                .Font.Bold = True 
                .Font.ColorIndex = 2 
            End With 
             
        Case "F" 
            With rng 
                .Interior.ColorIndex = 5 
                .Font.Bold = True 
                .Font.ColorIndex = 2 
            End With 
             
             ' clear formatting for any other value
        Case Else 
            With rng 
                .Interior.ColorIndex = xlNone 
                .Font.ColorIndex = 0 
                .Font.Bold = False 
            End With 
        End Select 
    Next rng 
End Sub


i have this code to do cond format, but it is taking at least 10 mins to calculate this sheet

Any ideas ?

Merc
 
Upvote 0
is there any way of if a Cell, in Column C,D,H,I,J is changed then update that row only, instead of updating whole range everytime

Just a thought not sure if possible ?

Merc

Just thought as this is a shared sheet on a network drive, the function i have above ,will not work unless they all have this udf installed on there pc's

Can i embed this function into this sheet, as i know a lot of the people who will share it will, not be upto installing a function.


Merc
 
Upvote 0
any one have any ideas as tryed select case type , but takes 10mins plus to run as rng is so large ?

Merc
 
Upvote 0
Code: 
Private Sub Worksheet_Calculate()
    Dim rng As Range
     
     ' only check the range named "rngDate"
    For Each rng In Range("rngDate")
        Select Case rng.Value
        Case "A", "AF", "AR"
            With rng
                .Interior.ColorIndex = 43
                .Font.Bold = True
                .Font.ColorIndex = 0
            End With
        Case "R"
            With rng
                .Interior.ColorIndex = 3
                .Font.Bold = True
                .Font.ColorIndex = 2
            End With
             
        Case "P", "PR"
            With rng
                .Interior.ColorIndex = 45
                .Font.Bold = True
                .Font.ColorIndex = 2
            End With
             
        Case "F"
            With rng
                .Interior.ColorIndex = 5
                .Font.Bold = True
                .Font.ColorIndex = 2
            End With
             
             ' clear formatting for any other value
        Case Else
            With rng
                .Interior.ColorIndex = xlNone
                .Font.ColorIndex = 0
                .Font.Bold = False
            End With
        End Select
    Next rng
End Sub


is there any way of making this only look at cells that have a value and not every cell in its range


as the actual rngDate is $L3:$FB1038

Merc
 
Upvote 0
how can i change the above code to only calculate the changed cell, anyone point me in right direction


Merc
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Question
Could you please help me about the vba code?
Replies
1
Views
168
Joe4
Joe4
N
  • Locked
  • Question
Turn .BODY outlook email into HTMLBODY using VBA
Replies
2
Views
402
RoryA
RoryA
N
  • Question
Turn .BODY subject body in outlook into HTMLBODY using VBA
2
Replies
10
Views
818
nealmaher
N
H
  • Question
Make an slicer in vba excel automatically
Replies
1
Views
229
Domenic
D
C
  • Question
VBA-Photos to be imported from Sharepoint Site rather than local drive
Replies
1
Views
242
Micron
M

Forum statistics

Threads
1,216,640
Messages
6,131,864
Members
449,680
Latest member
Manu556

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top