Solved

Fill with background color

Posted on 2015-02-22
11
196 Views
Last Modified: 2016-02-10
Hi,
I can see the background color of the cells is like the attached. How to put the same background color to other cells, using VBA codes?
t991.png
0
Comment
Question by:HuaMinChen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40625174
BTW, when running "CopyFromRecordset" within VBA codes, is there any way to get known how many records have been retrieved?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40625190
to set the color of a cell (range), you use:
cell.Interior.Color = vbGreen
or any other RGB color index
cell.Interior.Color = RGB(127, 187, 199)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40625192
>when running "CopyFromRecordset" within VBA codes, is there any way to get known how many records have been retrieved?

only from checking the recordset property (rowcount) after the action;
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Author Comment

by:HuaMinChen
ID: 40625195
what should be the syntax of rowcount? Thanks
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40625201
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40625370
Hi,
I'm retrieving records from AS 400, using these

    Dim cn As ADODB.Connection
    Dim strCNT As String, strSQL As String, strSQL2 As String, strSQL3 As String, strSQL4 As String, strSQL5 As String, strSQL6 As String
    Dim cnt As Integer, rw As Integer
    Dim rs4 As ADODB.Recordset
    Dim rs5 As ADODB.Recordset
    Dim rs6 As ADODB.Recordset
    
    strCNT = IS5ConnectionString()
    Set cn = CreateObject("ADODB.Connection")
    cn.ConnectionTimeout = SQL_TIMEOUT
    cn.Open strCNT
    
    strSQL4 = "SELECT SAAB,SAAN,SAAS,SAAPP,SABRNM,c.scact,SAPOD,SAPBR,SAPSQ,SAVFR,SADRF,SATCD,SACCY,SAAMA FROM " & HOST_LIB_EQ & "." & TBL_FM & " a," & HOST_LIB_EQ & "." & "scpf c where length(ltrim(rtrim(cast(SAPOD as char(12)))))=7 and substring(ltrim(rtrim(cast(SAPOD as char(12)))),2,6)='150130' and ltrim(rtrim(SACCY))='USD' and ltrim(rtrim(a.saan))=ltrim(rtrim(c.scan)) and ltrim(rtrim(a.saas))=ltrim(rtrim(c.scas)) and ltrim(rtrim(SAPBR))='@@AR'"
    
    Set rs4 = CreateObject("ADODB.Recordset")
    rs4.Open strSQL4, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    
    Worksheets("EQ System Gen FX").Cells.ClearFormats
    Worksheets("EQ System Gen FX").Cells(2, 1).Value = "Overnight Position Revaluation"
    Worksheets("EQ System Gen FX").Range("A5").CopyFromRecordset rs4
    cnt = rs4.RecordCount
...

Open in new window


I don't know why cnt in above does return "-1", while I can see there are 18 records retrieved already.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40625450
I guess this is due to:
adOpenForwardOnly

which may have the effect of not filling that property
I don't see any other solution ...
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40627331
Thanks. One last thing, what to assign to the cell if I expect to bracket the amount, when its value is negative?
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40627501
the format needs to be like this:
#.##0;(-#.##0)

in vba, you assign that to the numberFormat property of the range:

  Dim r As Range
  Set r = Range("B:B")
  r.NumberFormat = "#.##0;(-#.##0)"

Open in new window

0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40627574
Many thanks. I put these

    Range("N" & (11 + cnt + cnt2)).NumberFormat = "#.##0;(-#.##0)"
    Range("N" & (11 + cnt + cnt2)).Formula = "=Sum(N" & (11 + cnt) & ":N" & (10 + cnt + cnt2) & ")"
...

Open in new window


but it is showing

(-9278769.0)

which is confusing, right?
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40627603
but what do you want? without the minus sign?
in which case you just remove the " - " from the format
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you upgrade from Windows 8 to 8.1 or to Windows 10 or if you are like me you are on the Insider Program you may find yourself with many 450MB recovery partitions.  With a traditional disk that may not be a problem but with relatively smaller SS…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question