Conditional Formatting - multples from vba

Mornin all. ;0

I have a range of cells and a variety of conditions that I'm trying to create.

Specifically, if a number is below a range, do nothing, if it's within a range, I need to to change the font and color, and if it exceeds a range, I need it to change font and color yet again. (bold/orange and bold/red respectivly)

and I need to do this 22 times, once per row in range j6:p27

I tried various combinations (wish I'd saved the code) but all I could get was an effect that turned everything black.

Can someone please help me with this?

Code:

---

rng.interior.colorindex=??

---

Record macros, experiment... thats the background

Code:

---

rng.font.colorindex = ?? 'guessing

---

I think this is the font colour - you can check with a recording macro.


If you want you should be able to set conditional formating for the ranges though.

my fault for not clarifying, it's the code itself I need. I don't know how to code this in excel. I can record a macro but it acts like a drone, doing the same thing over and over again, regardless of the cell contents.

this will trigger on selection change....

put the code in "ThisWorkbook"
then change the numbers to what u want to test for

VB Code:

Dim WithEvents CWS As Worksheet
 
Private Sub CWS_SelectionChange(ByVal Target As Range)
    
    For x = 10 To 16
        For I = 6 To 27
                If CWS.Cells(I, x) >= 10 And CWS.Cells(I, x) <= 25 Then
                    CWS.Cells(I, x).Interior.ColorIndex = 46
                    CWS.Cells(I, x).Font.Bold = True
                ElseIf CWS.Cells(I, x) > 25 Then
                    CWS.Cells(I, x).Interior.ColorIndex = 3
                    CWS.Cells(I, x).Font.Bold = True
                    
                End If
        Next
    Next
    
    
End Sub
 
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Set CWS = Sh
End Sub

Alba:
sorry :) Yeah you need to use the code but reformat it to do what you want.
I usually do this by changing the selection object to proper objects and using a start position.

For example:
Create this code, create a toolbar button that runs the macro.
Click various cells and then the toolbar button.

Code:

---

Public Sub tellme()
    Dim wrk As Workbook
    Dim sht As Worksheet
    Dim rng As Range

    Set wrk = Application.Workbooks(1)
    Set sht = wrk.Sheets(1)
    Set rng = ActiveCell
   
    MsgBox "The Address is " & rng.Address(False, False)
   
    Set rng = Nothing
    Set sht = Nothing
    Set wrk = Nothing
End Sub

---

The rest is experimenting and getting used to Excels objects :)