condition between number in macro

sksanjeev786

Well-known Member
Joined
Aug 5, 2020
Messages
961
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Team

I have a below macro but I need condition between

1. =+10 and above Green
2. =-10 and above = Red
3.=+5 and +9= light green
4.=-5 and -9 =light Red.

and also while running this below macro it is running for all slide I need for certain slides like (slide 6,10,12)

Thanks in advance.

Rich (BB code):
Sub colorformat()

   Dim s As Slide
   Dim oSh As Shape
   Dim oTbl As Table
   Dim lRow As Long
   Dim lCol As Long
    Dim cell As ReflectionFormat
  
   For Each s In ActivePresentation.Slides
       For Each oSh In s.Shapes
           If oSh.HasTable Then
               Set oTbl = oSh.Table
               For lRow = 2 To oTbl.Rows.Count
                   For lCol = 3 To oTbl.Columns.Count
                     
                       If IsNumeric(oTbl.cell(lRow, lCol).Shape.TextFrame.TextRange) = True Then
                      
                       If oTbl.cell(lRow, lCol).Shape.TextFrame.TextRange >= 5 Then
                       oTbl.cell(lRow, lCol).Shape.Fill.ForeColor.RGB = RGB(102, 228, 102)
                       oTbl.cell(lRow, lCol).Shape.TextFrame.TextRange.Font.Color.RGB = RGB(255, 255, 255)
                        Else
                        If oTbl.cell(lRow, lCol).Shape.TextFrame.TextRange <= -5 Then
                        oTbl.cell(lRow, lCol).Shape.Fill.ForeColor.RGB = RGB(237, 102, 102)
                        oTbl.cell(lRow, lCol).Shape.TextFrame.TextRange.Font.Color.RGB = RGB(255, 255, 255)
                        Else
                        If oTbl.cell(lRow, lCol).Shape.TextFrame.TextRange = Null Then
                        End If

                        End If
                        End If
                        End If
                       
                   Next
               Next
           End If
       Next    ' Shape
   Next s
End Sub
 
Last edited by a moderator:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Hi Team,

Can anyone help me with this

Thanks in advance.
Thanks.
Regards,
sanjeev
 
Upvote 0
Hi Team

I have a below macro but I need condition between

1. =+10 and above Green
2. =-10 and above = Red
3.=+5 and +9= light green
4.=-5 and -9 =light Red.

and also while running this below macro it is running for all slide I need for certain slides like (slide 6,10,12)

Thanks in advance.

Rich (BB code):
Sub colorformat()

   Dim s As Slide
   Dim oSh As Shape
   Dim oTbl As Table
   Dim lRow As Long
   Dim lCol As Long
    Dim cell As ReflectionFormat
 
   For Each s In ActivePresentation.Slides
       For Each oSh In s.Shapes
           If oSh.HasTable Then
               Set oTbl = oSh.Table
               For lRow = 2 To oTbl.Rows.Count
                   For lCol = 3 To oTbl.Columns.Count
                    
                       If IsNumeric(oTbl.cell(lRow, lCol).Shape.TextFrame.TextRange) = True Then
                     
                       If oTbl.cell(lRow, lCol).Shape.TextFrame.TextRange >= 5 Then
                       oTbl.cell(lRow, lCol).Shape.Fill.ForeColor.RGB = RGB(102, 228, 102)
                       oTbl.cell(lRow, lCol).Shape.TextFrame.TextRange.Font.Color.RGB = RGB(255, 255, 255)
                        Else
                        If oTbl.cell(lRow, lCol).Shape.TextFrame.TextRange <= -5 Then
                        oTbl.cell(lRow, lCol).Shape.Fill.ForeColor.RGB = RGB(237, 102, 102)
                        oTbl.cell(lRow, lCol).Shape.TextFrame.TextRange.Font.Color.RGB = RGB(255, 255, 255)
                        Else
                        If oTbl.cell(lRow, lCol).Shape.TextFrame.TextRange = Null Then
                        End If

                        End If
                        End If
                        End If
                      
                   Next
               Next
           End If
       Next    ' Shape
   Next s
End Sub
Hi Team,

Any help on the above query condition.

Thanks in advance.
sanjeev
 
Upvote 0
Hi Team,

To simplify the above macro i need data with the below condition as of now I have only greater than and less than 5 macro

1. =+10 and above Green
2. =-10 and above = Red
3.=+5 and +9= light green
4.=-5 and -9 =light Red.


If oTbl.cell(lRow, lCol).Shape.TextFrame.TextRange >= 5 Then

If oTbl.cell(lRow, lCol).Shape.TextFrame.TextRange <= -5 Then
 
Upvote 0
Hi Team,

To simplify the above macro i need data with the below condition as of now I have only greater than and less than 5 macro

1. =+10 and above Green
2. =-10 and above = Red
3.=+5 and +9= light green
4.=-5 and -9 =light Red.


If oTbl.cell(lRow, lCol).Shape.TextFrame.TextRange >= 5 Then

If oTbl.cell(lRow, lCol).Shape.TextFrame.TextRange <= -5 Then
HI Team,

Anyone have solution

Thanks
Sanjeev
 
Upvote 0

Forum statistics

Threads
1,223,248
Messages
6,171,027
Members
452,374
Latest member
keccles

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