Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 10th, 2017, 12:06 PM
Registered User
 
Join Date: Oct 2017
Posts: 11
Thanks: 1
Thanked 0 Times in 0 Posts
Default If cell is any color then...

Howdy all.
This is my first time posting, and I am very beginner level so bear with me....
I am trying to write code that says if any of the cells between B1 and D1 are filled with ANY color, then fill A1 a gray...
Any Thoughts?
 
Old October 10th, 2017, 12:13 PM
Authorized User
 
Join Date: Oct 2015
Posts: 48
Thanks: 0
Thanked 5 Times in 5 Posts
Default

White/default color has a specific color code, so you want to check if the color code is different than white code then do any action.
To find out what colour is white, record a macro, change to any color then reset to non-color.

I believe you do it on:
<>cell.interiorIndex.color.<\>
__________________
Nostalgia 4 Infinity
 
Old October 10th, 2017, 12:52 PM
Registered User
 
Join Date: Oct 2017
Posts: 11
Thanks: 1
Thanked 0 Times in 0 Posts
Default

I already have the code for white as RGB (255, 255, 255), and the gray color I want is RGB (166, 166, 166) but I can't figure out the code... This is all I can think of so far....
Code:
If *any cell withinin* Range(B1:D1).interior.color <> RGB (255, 255, 255) 
Then Range (A1).Interior.Color = RGB (166, 166, 166)
End If
 
Old October 10th, 2017, 01:19 PM
Registered User
 
Join Date: Oct 2017
Posts: 11
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Okay, my code is a little bit better now. Here is the entire sub. Still stuck...
Code:
Sub MarkAccountNumber()
'Auto fit width on all columns

    Range("A:A, DA:DA").EntireColumn.AutoFit

' Find last row

    Dim LastRow As Long             'Variable for last data row
    LastRow = Range("B5000").End(xlUp).Row

' Find first data row of spreadsheet

    Dim FirstRow As Integer         'Variable for first data row

    Cells.Find(What:="Account Number", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    FirstRow = ActiveCell.Offset(1, 0).Row

 Cells(1, "A").Activate

 'Call out variables from Excel

        Dim i As Integer

For i = FirstRow To LastRow
   *THIS LINE IS ERRORING*  If Range("C & i : CK & i").Interior.Color <> RGB(255, 255, 255) Then
    Range("A & i").Interior.Color = RGB(166, 166, 166)
    End If
    
Next i
    MsgBox "Done"
 
Old October 10th, 2017, 04:04 PM
Authorized User
 
Join Date: Oct 2015
Posts: 48
Thanks: 0
Thanked 5 Times in 5 Posts
Default

Been posting but it is somehow not updating.
Heres the code below.

Sub Macro1()
Dim WB As Workbook
Dim WS As Worksheet
Dim myCell As Integer
Dim myWhite As Integer, myGrey As Integer

Set WB = Application.ThisWorkbook
Set WS = WB.ActiveSheet
myWhite = -4142 ' None Colored
myGrey = 16 ' A shade of grey

'Debug.Print WS.Cells(9, 3).Interior.ColorIndex

For i = 2 To 12
myCell = WS.Cells(i, 3).Interior.ColorIndex
If myCell <> myWhite Then
WS.Cells(i, 1).Interior.ColorIndex = myGrey
Debug.Print WS.Cells(i, 3).Interior.ColorIndex
End If
Next

End Sub
__________________
Nostalgia 4 Infinity

Last edited by Zakalwe; October 10th, 2017 at 05:00 PM..
The Following User Says Thank You to Zakalwe For This Useful Post:
onexmadxdisaster (October 10th, 2017)
 
Old October 10th, 2017, 04:28 PM
Registered User
 
Join Date: Oct 2017
Posts: 11
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Wonderful! Thank you SOOO much Zakalwe!
I am definitely trying to compile and debug as often as I can, but I keep getting personal roadblocks in determining what the specific issue is.
Do you mean the immediate window? I am still trying to learn this as well.
What is the statement Debug.Print? Not sure I am following how to use this either.
 
Old October 10th, 2017, 04:46 PM
Authorized User
 
Join Date: Oct 2015
Posts: 48
Thanks: 0
Thanked 5 Times in 5 Posts
Default

Code:
Sub Macro1()
Dim WB As Workbook
Dim WS As Worksheet
Dim myCell As Integer
Dim myWhite As Integer, myGrey As Integer

Set WB = Application.ThisWorkbook
Set WS = WB.ActiveSheet
myWhite = -4142 ' None Colored
myGrey = 16     ' A shade of grey

'Debug.Print WS.Cells(9, 3).Interior.ColorIndex

For i = 2 To 12
    myCell = WS.Cells(i, 3).Interior.ColorIndex
    If myCell <> myWhite Then
        WS.Cells(i, 1).Interior.ColorIndex = myGrey
        Debug.Print WS.Cells(i, 3).Interior.ColorIndex 'This is what you see in the intermediate Windw.
    End If
Next
  
End Sub
__________________
Nostalgia 4 Infinity
 
Old October 10th, 2017, 04:47 PM
Authorized User
 
Join Date: Oct 2015
Posts: 48
Thanks: 0
Thanked 5 Times in 5 Posts
Default

Code:
Sub Macro1()
Dim WB As Workbook
Dim WS As Worksheet
Dim myCell As Integer
Dim myWhite As Integer, myGrey As Integer

Set WB = Application.ThisWorkbook
Set WS = WB.ActiveSheet
myWhite = -4142 ' None Colored
myGrey = 16     ' A shade of grey

'Debug.Print WS.Cells(9, 3).Interior.ColorIndex

For i = 2 To 12
    myCell = WS.Cells(i, 3).Interior.ColorIndex
    If myCell <> myWhite Then
        WS.Cells(i, 1).Interior.ColorIndex = myGrey
        Debug.Print WS.Cells(i, 3).Interior.ColorIndex
    End If
Next
  
End Sub
__________________
Nostalgia 4 Infinity
 
Old October 10th, 2017, 04:49 PM
Authorized User
 
Join Date: Oct 2015
Posts: 48
Thanks: 0
Thanked 5 Times in 5 Posts
Default

Sub Macro1()
Dim WB As Workbook
Dim WS As Worksheet
Dim myCell As Integer
Dim myWhite As Integer, myGrey As Integer

Set WB = Application.ThisWorkbook
Set WS = WB.ActiveSheet
myWhite = -4142 ' None Colored
myGrey = 16 ' A shade of grey

'Debug.Print WS.Cells(9, 3).Interior.ColorIndex

For i = 2 To 12
myCell = WS.Cells(i, 3).Interior.ColorIndex
If myCell <> myWhite Then
WS.Cells(i, 1).Interior.ColorIndex = myGrey
Debug.Print WS.Cells(i, 3).Interior.ColorIndex
End If
Next

End Sub
__________________
Nostalgia 4 Infinity
 
Old October 10th, 2017, 04:52 PM
Authorized User
 
Join Date: Oct 2015
Posts: 48
Thanks: 0
Thanked 5 Times in 5 Posts
Default

Been posting but it looks like the page is slow to update.
Don't be surprised if you see several posts of the code.
__________________
Nostalgia 4 Infinity





Similar Threads
Thread Thread Starter Forum Replies Last Post
How to get Excel Cell color populate .NET Framework 2.0 2 March 3rd, 2014 04:25 AM
need help with fill in cell color pooky Excel VBA 1 July 29th, 2012 09:58 PM
change cell color martin1 Visual Basic 2005 Basics 2 October 4th, 2006 08:55 AM
Change Cell Color phungleon HTML Code Clinic 2 April 9th, 2006 12:36 AM
Listview Cell Color leontun Pro VB 6 2 December 15th, 2004 04:14 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.