Microsoft Excel - Macros for Formatting Cells - Asked By Subash D on 18-Jun-12 09:10 AM

Jitendra Faye replied to Subash D on 18-Jun-12 09:22 AM
Follow this sample Macro to format cell.

reference from-

http://support.microsoft.com/kb/213616

Solution-

Sub ShadeEveryOtherRow()
    Dim Counter As Integer

    'For every row in the current selection...
    For Counter = 1 To Selection.Rows.Count
      'If the row is an odd number (within the selection)...
      If Counter Mod 2 = 1 Then
        'Set the pattern to xlGray16.
        Selection.Rows(Counter).Interior.Pattern = xlGray16
      End If
    Next

End Sub
     

D Company replied to Subash D on 18-Jun-12 10:17 AM
In which format you want?

generally we used to do like this
//sample example for number formatting
With Selection
      .Range("A1:B1").EntireColumn.NumberFormat = "0.00%"
      .Range("C1:G1").EntireColumn.Formula = "#,##0"
    End With

Regards
D
Pichart Y. replied to Subash D on 18-Jun-12 11:54 AM
Hi Subash D.

Try this, not sure if I under stand you correctly, anyway the code is there you can adjust it as you want...
I believe you can understand this simple code, just loop with i and K to identify the cell with step 2

then select the case to fill the format the cell and font... :)

Code this here....

Sub formatMatrix()




For i = 11 To Range("A" & Rows.Count).End(xlUp).Row Step 2
    For k = 3 To Cells(11, Columns.Count).End(xlToLeft).Column Step 2
    
        Select Case Cells(i, k)
        Case "IL"
            With Range(Cells(i, k).Address & ":" & Cells(i, k + 1).Address)
                .Interior.Color = 15773696     'blue
                .Font.Color = 15773696
            End With
            With Range(Cells(i + 1, k).Address & ":" & Cells(i + 1, k + 1).Address)
                .Interior.Color = 49407     'blue
                .Font.Color = 49407
            End With
        
        Case "LL"
            With Range(Cells(i, k).Address & ":" & Cells(i + 1, k + 1).Address)
                .Interior.Color = 49407
                .Font.Color = 49407
            End With
        
        Case "AL"
            With Range(Cells(i, k).Address & ":" & Cells(i + 1, k + 1).Address)
                .Interior.Color = 49407
                .Font.Color = 49407
            End With
            With Range(Cells(i, k + 1).Address)
                .Interior.Color = 15773696
                .Font.Color = 15773696
            End With
        
        Case "NO"
            With Range(Cells(i, k).Address & ":" & Cells(i + 1, k + 1).Address)
                .Interior.Color = 15773696
                .Font.Color = 15773696
            End With
        
        Case "EL"
            With Range(Cells(i, k).Address & ":" & Cells(i + 1, k + 1).Address)
                .Interior.Color = 15773696
                .Font.Color = 15773696
            End With
            
            With Range(Cells(i + 1, k + 1).Address)
                .Interior.Color = 49407
                .Font.Color = 49407
            End With
        
        End Select
    Next k
Next i
End Sub
------------------------.---------------------------------
This is attachment --> Vba_Conditionformat-Skill Matrix.zip

pichart Y, 
Subash D replied to D Company on 19-Jun-12 01:21 AM
Hi Pichart,

Thanks for the help. But I believe some corrections needs to be done to the code. Attached is the actual sheet where I need to run the Macro.

Iam not so good in writing codes. Can you help me in changing the codes for this sheet.

Thanks
Subash D

Skill Matrix Macro.zip
Subash D replied to Pichart Y. on 25-Jun-12 08:17 AM
Hi Pichart,

Thanks for the help. But I believe some corrections needs to be done to the code. Attached is the actual sheet where I need to run the Macro.

Iam not so good in writing codes. Can you help me in changing the codes for this sheet.

Thanks
Subash D

Pichart Y. replied to Subash D on 25-Jun-12 11:05 AM
Hi Subash D,

Here it is... --->Vba_Skill Matrix(version2).zip

Try this, I don't know why your sample color seems to be incorrect...I don't know if I understand correctly...

please feed  back.

Pichart Y.
Subash D replied to Pichart Y. on 26-Jun-12 01:50 AM
Thanks Pichart.

Subash D