Microsoft Excel - Macros for Formatting Cells - Asked By Subash D on 18-Jun-12 09:10 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

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, 

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

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

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.

Thanks Pichart.

Subash D